VLookUp Tables

[Guest]

Hi all

I have created a VLookUp table and it works fine, however, I was wondering
if it is possible to ask Excel to return two values from two columns in the
look-up table rather than just one.

=VLOOKUP(A1,lookup,1 and 3)

Is there any way this is possible?

Also, can you nest VLookUp tables??

Thank you.

Louise

 

[Pete_UK]

Hi Louise,

I'm not sure why you would want to return from column 1 of the lookup
table, as that will be the same as A1 in your formula. You would need
to do something like this:

=VLOOKUP(A1,lookup,1,0) + VLOOKUP(A1,lookup,3,0)

assuming you want an exact match. You might need to change the + to &
if the returned values are text.

Not sure what you mean by nesting the tables - if you have two or more
tables that the lookup value could be in, then generally you would have
something like:

=IF(ISNA(vlookup_1),IF(ISNA(vlookup_2),IF(ISNA(vlookup_3),"not
present",vlookup_3),vlookup_2),vlookup_1)

where vlookup_n is a VLOOKUP formula referring to table n.

Hope this helps.

Pete

 

[Guest]

Thanks for your reply.
I've just tried using 2 lookups as you have shown below and i get an error
message, rather than it returning two values. My formula reads;

=VLOOKUP(D5,test,2) + VLOOKUP(D5,test,3)

what have I done wrong??

Louise

 

[Pete_UK]

What error message do you get?

If it is #N/A then it means that the value in D5 has not been found in
test (i.e. it is less than the first value in the table). If it is
#VALUE then you will need to change the + to &, as one of the returned
values will be text.

Please advise.

Pete

 

[Guest]

The error was #VALUE so I have changed the + to & and it worked perfectly,
thank you. I didn't know you could use two LOOKUPS at the same time.

Louise.

 

[Pete_UK]

Glad to be of help - thanks for feeding back. You might like to make it
&" - "& to separate the two values visually.

Pete