vlookup - bugs between the sheets!

Sorry a few typos... here we go again
the formulae in C2 and C3 of sheet 1 don't work, but C1 does --- help!!!
sheet1:
...A..B............C
----------------------------
1| a1 1=MID(A1,2,1) one =VLOOKUP(1,Sheet2!A1:B3,2,0)
2| a2 2=MID(B1,2,1) #N/A =VLOOKUP(MID(A2,2,1),Sheet2!A1:B3,2,0)
3| a3 3=MID(C1,2,1) #N/A =VLOOKUP(B3,Sheet2!A1:B3,2,0)

sheet2:
...A..B...C
-------------------
1| 1 one
2| 2 two
3| 3 three

Stef

If I'm understanding your problem situation correctly, you have in the range A1:B3 on Sheet2 your lookup table ({1,2,3;one,two,three}). Select these cells and name the selected range DTABLE via the Name Box or via Insert|Name|Define.

On Sheet1, you have the values {"a1","a2","a3"}, the last char of each you want to use as the lookup value.

On Sheet1,

in B1 enter: =VLOOKUP(VALUE(RIGHT(A1,1)),DTABLE,2,0) [ Copy down this formula to B2:B3 ]

Note. The 4th argument of VLOOKUP, which is 0 meaning FALSE, enforces an exact match. Change it to 1 or TRUE or omit it if you want an approximate match.DTABLE is equivalent to Sheet2!A1:B3. Moreover, RIGHT(A1,1) is equivalent to your MID(A1,2,1). It's a normal behavior to get #N/A if the lookup value such as VALUE(RIGHT(A1,1)) is not available in column 1 of DTABLE. If it's available, you'll get the value associated with it.

Aladin

Aladin

yep it works! thank you very much.
So the lesson is that- if the vlookup value is an expression rather than a literal value, and the table being referenced is on another sheet, the table must be named, and the value expression must be evaluated to a number type.

cheers STEF