VBA complex vlookup

Okay I saw a number of questions that were similar to mine but none were quite exact So the answers didn't help. Although it may not be exactly a vlookup the process is similar to one so that was the closest thing I could think of as a title.

I have a column (A) of values in document A and I want excel to go through each value and look in document B to find the same value in column (A). For each value it finds I want to do three things:

  1. I want it to grab the value two cells to the right of each matched value found in document B and paste it one cell to the right of the value in column A of document A

  2. I want it to look at the value three to the right of each matched value found in document B and if that value is "time" put the letter "T" two cells to the right of the value in column A of document A.

  3. if the value four cells to the right of each matched value found in document B is > 5 I want excel to fill the cell three cells to the right of the value in column A of document A with a formula = the value of (column G / column H) in document B for that same row.

In case my wording was confusing I basically want to do a more complex vlookup. Document A has only column A and I want to look at column A to fill in columns B, C, and D using the table in document B

Please let me know if you have any questions and thanks in advance for any help you can give me.

Answers


Each of the things you want to do starts out as a simple "VLOOKUP", but some have a twist. To simplify things, we should name the ranges "A" and "B". You do this by selecting all the cells that belong to "A", then typing a name in the address box (top left of your screen). Let's call it "RangeA". Similarly, select all the cells belonging to region B (first column through eighth column; make sure not to include headers), and call it RangeB. Now we can begin:

"For each value in A, find corresponding value in B, and return the value two to the right":

This is a simple VLOOKUP. Enter this formula in the cell to the right of A (B1, probably):

=VLOOKUP(A1, RangeB, 3, FALSE)

Explanation: look up the value in Cell A1 in the range called RangeB. Find an exact match (that's the "FALSE"). Return the corresponding value in column 3 (two to the right). Drag the formula all the way down (shortcut: double click the little square in the bottom right corner of the cell when it's selected).

I want it to look at the value three to the right of each matched value found in document B and if that value is "time" put the letter "T" two cells to the right of the value in column A of document A.

This is a VLOOKUP followed by an IF. Put the following formula two cells to the right of A (C1, probably) and drag down as before:

=IF(VLOOKUP(A1, RangeB, 4, FALSE) = "time", "T", "")

Explanation: look up as before (but column 4, i.e. "three to the right"). Test the value found. If it's "time", enter "T" in this cell; otherwise, enter "" (i.e. nothing).

if the value four cells to the right of each matched value found in document B is > 5 I want excel to fill the cell three cells to the right of the value in column A of document A with a formula = the value of (column G / column H) in document B for that same row.

Find the match, take the ratio. It would be faster to do the lookup just once with a MATCH function (in a hidden column on the sheet) so we have a reference to the row number (we need it three times), but it's not necessary. Unless your sheet has thousands of rows you will not notice the performance difference. According to this thread there's no "quick way" to achieve this inside a formula.

=if(VLOOKUP(A1, RangeB, 5, FALSE) > 5, VLOOKUP(A1, RangeB, 7, FALSE)/VLOOKUP(A1, RangeB, 8, FALSE), "")

And there you have it. Note that INDEX(MATCH... (the preferred method of @user2140261) can achieve the same thing as VLOOKUP but it involves two functions. But if you decide to create a hidden column with the MATCH function in it, you can then use INDEX functions to achieve all the other things - and it would be the most efficient since you only use one MATCH (as opposed to recomputing it one or more times for each column).

I hope you can figure it out from here.


Need Your Help

Passing this pointer to inner class as a weak_ptr

c++ c++11

I am trying the figure out a way to use shared_ptr and weak_ptr in my code. I have two classes - Class One and Class Two. Two in an inner class of One. Class two's constructor takes in a weak_ptr of