Combining VLOOKUP with MID function

mellen

New Member
Joined
Apr 12, 2005
Messages
35
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Nogslaw

Well-known Member
Joined
Jul 9, 2004
Messages
904
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:

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

mellen

New Member
Joined
Apr 12, 2005
Messages
35
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,658
Messages
5,597,403
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top