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
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