Value lookup

[Andreas Håkansson]

Hi,

I want to search for a row in a matrix where column A contains value X and
return the value i column B on the same row. So if I have the following
matrix

A B
------
1 AA
2 BB
3 CC
4 DD
5 EE


And I want to look up the row with Column A value of "3" which would get me
the row

3 CC

I would then like ot return the value of Column B from that row which means
I would like to get

CC

returned.

On one worksheet I have a matrix of data and on another worksheet I add
values in a field and want another field to
automatically be filled with the correct lookup value. I know this has to be
dead easy but I just can't get a formula to
work =/

Thanks!

 

[Niek Otten]

=VLOOKUP(A1,Sheet4!A1:B5,2,FALSE)

 

[Andreas Håkansson]

Nevermind, figured it out =)

 

[Bondi]

Hi,

You should use VLOOKUP()

=VLOOKUP(value to lookup,Array to lookup in,column to return,FALSE)

so that in above it would be: (if 1 is in cell A1 and AA in cell b1)

=VLOOKUP(3,A2:B5;2,FALSE)

this would return CC in the cell of the VLOOKUP() function

Regards,
Bondi

 

[Andreas Håkansson]

Thanks Niek and Bondi

I noticed something strange. If I made a region of A1:B5 and use the same
lookup then it will ALWASY return
the last row.

=VLOOKUP(A1,MyRange,2,FALSE)

Any ideas why ?

 

[Andreas Håkansson]

Hmm I narrowed it down abit more. My matrix is actually abit more complex
when what I illustrated (I thought I could illustrate a simplified model and
apply it to my scenario). It appears that the last row is returned if my
matrix contains more then two columns. Try this one

A B C
----------------------
1 30000001 AA
2 30000002 BB
3 30000003 CC
4 30000004 DD
5 30000005 EE
6 30000006 FF
7 30000007 GG
8 30000008 HH
9 30000009 II
10 30000010 JJ
11 30000011 KK


Now if I use

=VLOOKUP(30000005; A1:C11; 3; FALSE)

it will return KK instead of the expected EE. If I narrow it down to

=VLOOKUP(30000005; B1:C11; 3; FALSE)

it works as expected. Problem is that my matrix contains 15 columns or so
and I want to match a value in column 2 and return value from column 13

 

[Guest]

Hi

Your 'lookup' value must be in the first column. The value you want to
return can be in any column to the right of your 'lookup' value. Use the
range B1:N100 and the formula:
=VLOOKUP(30000005; B1:N100; 13; FALSE)

Hope this helps.
Andy.

 

[Andreas Håkansson]

Found out why. VLOOKUP searched column 1. Figured out a workaround =)