Combining VLOOKUP with MID function

[mellen]

Hi,

I have some data like the below, let's call this Range1
1 C
2 D
3 E
4 F

I also have some other data (in a different sheet) like the below, let's call this Range2
C OtherCodeA Option1_item
D OtherCodeB Option2_item
E OtherCodeC Option3_item
F OtherCodeD Option4_item

What I need to do is lookup the digit in Option1_item to then check the first set of data's letter, to then check the second set for the 'OtherCode', convoluted I know, but it's legacy and am not really keen on changing this.

I have VLOOKUP(MID(C1,7,1),Range1,2,FALSE), but I get #N/A (but expecting C).
When I separate the MID(C1,7,1), I get the expected result, 1.
When I substitute MID(C1,7,1), I get C, but combining doesn't give me any joy. I was thinking it could be cell formats etc, but have tried to no avail.

I was hoping to then expand the above to VLOOKUP(VLOOKUP(MID(C1,7,1),Range1,2,FALSE),Range2,2,FALSE), but need to conquer the first hurdle first.

Thanks in advance.

Len

 

[Nogslaw]

This is because you are trying to lookup a Text Value in a range of Numeric Values. Let's change your lookup to a numeric value:

=VLOOKUP(VALUE(MID(C1,7,1)),Range1,2,FALSE)

 

[mellen]

What a wally!! Really sorry, I tried doing it the other way and converted the looked up cells to text and tried the TEXT function...

Thanks!