VLookUp Tables
 

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

VLookUp Tables
 

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(vlo okup_3),"not
present",vlookup_3),vlookup_2),vlookup_1)

where vlookup_n is a VLOOKUP formula referring to table n.

Hope this helps.

Pete

Louise wrote:
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

VLookUp Tables
 

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" wrote:

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(vlo okup_3),"not
present",vlookup_3),vlookup_2),vlookup_1)

where vlookup_n is a VLOOKUP formula referring to table n.

Hope this helps.

Pete

Louise wrote:
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

VLookUp Tables
 

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

Louise wrote:
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" wrote:

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(vlo okup_3),"not
present",vlookup_3),vlookup_2),vlookup_1)

where vlookup_n is a VLOOKUP formula referring to table n.

Hope this helps.

Pete

Louise wrote:
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

VLookUp Tables
 

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" wrote:

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

Louise wrote:
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" wrote:

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(vlo okup_3),"not
present",vlookup_3),vlookup_2),vlookup_1)

where vlookup_n is a VLOOKUP formula referring to table n.

Hope this helps.

Pete

Louise wrote:
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

VLookUp Tables
 

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

Pete

Louise wrote:
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" wrote:

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

Louise wrote:
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" wrote:

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(vlo okup_3),"not
present",vlookup_3),vlookup_2),vlookup_1)

where vlookup_n is a VLOOKUP formula referring to table n.

Hope this helps.

Pete

Louise wrote:
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