VLookUp Tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
 
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
 
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.
 
Glad to be of help - thanks for feeding back. You might like to make it
&" - "& to separate the two values visually.

Pete
 
Back
Top