VLOOKUP - combine 2 cells in table array

[iainmartin100]

Hi all,

I'm stuck with a simple vlookup, well it would be simple if I knew the answer.

Im doing a lookup to an external source but the external source has the refrance I want to look up in coloumns A & B.

Example
If the lookup value of "1234" was in colomn A the below would be fine:
=VLOOKUP("1234",[Book1]Sheet1!$A$1:$C$20,3,FALSE)
But unfortunatly in Coloum A is "12" and in coloumn B is "34"
And I can't insert a new column at the start of the table combining the two as this is a daily generated file from a system.

Below is a simplifed example of the external data and what Im trying to lookup and return:-
A B C
1 12 34 V
2 23 45 W
3 34 56 X
4 45 67 Y
The answer in this case should be "V"
So how do I combine columns A&B on the table array?

Many thanks

 

[Aladin Akyurek]

Hi all,

I'm stuck with a simple vlookup, well it would be simple if I knew the answer.

Im doing a lookup to an external source but the external source has the refrance I want to look up in coloumns A & B.

Example
If the lookup value of "1234" was in colomn A the below would be fine:
=VLOOKUP("1234",[Book1]Sheet1!$A$1:$C$20,3,FALSE)
But unfortunatly in Coloum A is "12" and in coloumn B is "34"
And I can't insert a new column at the start of the table combining the two as this is a daily generated file from a system.

Below is a simplifed example of the external data and what Im trying to lookup and return:-
A B C
1 12 34 V
2 23 45 W
3 34 56 X
4 45 67 Y
The answer in this case should be "V"
So how do I combine columns A&B on the table array?

Many thanks

Try...

Control+shift+enter, not just enter:

=INDEX($C$2:$C$5,MATCH("1234",$A$2:$A$5&$B$2:$B$5,0))

 

[iainmartin100]

Perfect, thanks for your help.
Have to say iv'e never used the INDEX function so it's great to learn a new trick.

 

[Aladin Akyurek]

Perfect, thanks for your help.
Have to say iv'e never used the INDEX function so it's great to learn a new trick.

You are welcome. Thanks for providing feedback.