VLOOKUP confusion

My formula in C2 = =VLOOKUP(LEFT($A$2,3) & RIGHT($B$2,1),A9:C10,3,FALSE)

I need to join the left three characters from A2, the last character from B2 and use this in vlookup.

Can someone please explain what I am doing wrong?

Solution 1:

Under the case that the lookup values are not in the first column of table array.

You could also use Index + Macth to replace vlookup function.

Such as =INDEX($C$9:$C$10,MATCH(LEFT($A2,3)&RIGHT($B2,1),$B$9:$B$10,0)).

Solution 2:

While using VLOOKUP, The lookup must be in the first column as stated here:

lookup_value (required)

The value you want to look up. The value you want to look up must be in the first column of the range of cells you specify in the table_array argument.

In the case of your formula, your are searching the lookup values (job role (CONA or ConB)) on the job code column (101 or 102). They are not found, that's why you are getting #N/A

Job Role must be the first column or you need to offset it to be the first column.

Either use:

=VLOOKUP(LEFT(A2,3) & RIGHT(B2,1),B$9:C$10,2,FALSE)

OR

=VLOOKUP(LEFT(A2,3) & RIGHT(B2,1),OFFSET(A$9:C$10,0,1),2,FALSE)

You will almost certainly need $ in the cell references for the source table, but not for the cells used to provide the lookup key, as it would seem you want to copy this formula down from C2 to B3 (and beyond). Getting the $ wrong would mean it works on row 1 then breaks.