need help with vlookup

New Contributor

I am trying to have vlookup populate a column from a different sheet and I cant get it to work.

I provided some screen shot.

 

https://share.cleanshot.com/PmajU6

 

 

3 Replies

@Patrickwhalen10 

=VLOOKUP($A2,Test2!$A$2:$C$8,COLUMN(Test2!C1),FALSE)

You can try this formula.

vlookup.JPG

 

it seems to work,
can you break it down as to what you did so that I can recreate it and not just copy and paste the formula?

so basically what you used for:
Lookup_Value
Table_array
Col_index_Num
Range_lookup

@Patrickwhalen10 

Lookup_Value

$A2  The $ sign locks the lookup value in column A. If you enter the formula in cell C2 and drag it down or to the left or right the lookup value will be a cell in column A. The row number will change to e.g. $A3 or $A4 but the column A won't.

Table_array

Test2!$A$2:$C$8   The table_array has the 3 columns A, B and C and they are locked by the $ signs. It won't change no matter if you drag the formula up or down or left or right. Vlookup tries to look up the lookup value in range Test2!$A$2:$A$8. An exact match is done because of the range lookup FALSE.

Col_index_Num

COLUMN(Test2!C1)   This currently returns 3 because the number of column C is 3 in any sheet. If there is an exact match the corresponding entry of range Test2!$C$2:$C$8 is returned because Test2!$C$2:$C$8 is the 3rd column of the table array. C1 is not locked with the $ sign and therefore changed to B1 if you drag the formula from cell C2 to B2. In cell B2 the formula shows COLUMN(Test2!B1) with the result 2 because 2 is the number of column B. If there is an exact match the corresponding entry of range Test2!$B$2:$B$8 is returned because Test2!$B$2:$B$8 is the 2nd column of the table array ( Test2!$A$2:$C$8 ).

Range_lookup

FALSE   The VLOOKUP is looking for an exact match of the lookup value in range Test2!$A$2:$A$8 with this command.