# LOOKUP - referencing cells

1. ## LOOKUP - referencing cells

Hi,

I'm trying to return a value from a worksheet dependant on a value in a cell. The problem is (as I understand it) I can't use a VLOOKUP because the lookup value is not in the first column.

Here's kinda what my data looks like:

SHEET-1
A B C D - (columns)
1 2 ? ?
5 6 ? ?

SHEET-2
A B C D - (columns)
T 7 H P
R 2 F Z
L 9 V A
P 6 X W

I am trying to put a lookup in Cols C and D/Sh 1 which checks the value in Col B/Sh 1 against the value in Col B/Sh 2 and returns the values of Cols C and D in Sheet 2.

For example; if I looked up the value of cell B1 in Sheet 1 (i.e. the number 2) it would lookup the values in Cols C and D on Sheet 2 and return F and Z in columns C and D. If I looked up 6 (cell B2, Sheet 1) it would return X and W in C3 and C4 in Sheet 1.

Is there a smart way to do this?  Register To Reply

2. In C1 and copy across and down as necessary:

=VLOOKUP(\$B1,Sheet2!\$B\$1:\$D\$4,COLUMN()-1,FALSE)

You can use VLOOKUP because your lookup value is in the first column of the table in sheet2 - you can ignore the first column in sheet2.  Register To Reply

3. Hi,

Thaks for the repsonse. I'm still having trouble making it work though. I've transposed the formula you posted into my sheet like so:

=VLOOKUP(\$C3,Proximates!\$B\$1:\$D\$4,COLUMN()-1,FALSE)

One thing I've noticed is that in column B on sheet 2 the numbers have a ' in front of them i.e. '50-178. Would this screw with the lookup? I am a bit of an Excel n0ob so sorry if that's a dumb question.

You can use VLOOKUP because your lookup value is in the first column of the table in sheet2 - you can ignore the first column in sheet2.
Also, could you explain how this is true? The lookup value is in column B and the things I want returned are in C and D. Nothing's in column A that I'm working with.

I've only just started using VLOOKUPs so I'm still pretty bewildered by them. They're pretty funky but make my brain twitch whenever I try to do something fancy with them.

Cheers  Register To Reply

4. The solution was based on what you presented in the first post so perhaps you could attach a small sample of your actual workbook. The single speech marks indicate text.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

Col_index_num is the column number in table_array from which the matching value must be returned.

Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.  Register To Reply