VLOOKUP can't use value from formula for lookup value?

[PatRichard]

I have a product number that I use multiple cells to grab various characters from. So, example is UI1950202TB, and then one cell grabs the 'U', next cell grabs the 'I', etc. I'm trying to then use VLOOKUPs to take the values of those cells and populate others. It seems that for cells where the computed value is a number, it doesn't work. But cells where the computed value is letters, works fine.

If I hard code the numbers in those cells instead of calculating by using MID(), the VLOOKUPs work fine. It's just when I use a formula to determine their value that the numeric ones fail.

Ideas on workarounds?

 

[Aladin Akyurek]

The first one does, but the second one doesn't.

What do you have in Data!L2? And does what you have in Data!L2 occur in Data!A3:B36?

 

[PatRichard]

What do you have in Data!L2? And does what you have in Data!L2 occur in Data!A3:B36?

Data!L2 is 5 (it's calculated via =MID(Input!D3,5,1)). If I take out the formula and just put a 5 there, the VLOOKUP works fine. Yes, it occurs in A33.

I've uploaded a copy to http://innervation.com/crap/ProductNumberCalculatorv1.1.zip

I've added some minor error checking to the VLOOKUPs just for visual effect. But that hasn't made any difference.

 

[Aladin Akyurek]

MID formulas generate text value. The match range of your lookup tables are mostly numbers. So the MID outputs must be converted to numbers. What follows does just that.


D6 of Input >>

=IFERROR(VLOOKUP(Data!$I$2,Data!$A$2:$B$7,2,0),"")

D7 of Input >>

=IFERROR(VLOOKUP(Data!$J$2,Data!$A$10:$B$28,2,0),"")

D8 of Input >>

=Data!$K$2+0

D9 of Input >>

=IFERROR(VLOOKUP(Data!$L$2+0,Data!$A$31:$B$36,2,0),"")

D10 of Input >>

=IFERROR(VLOOKUP(Data!$M$2+0,Data!$A$39:$B$52,2,0),"")

D11 of Input >>

=IFERROR(VLOOKUP(Data!$N$2+0,Data!$A$55:$B$65,2,0),"")

D12 of Input >>

=IFERROR(VLOOKUP(Data!$O$2+0,Data!$A$68:$B$75,2,0),"")

D13 of Input >>

=IFERROR(VLOOKUP(Data!$P$2+0,Data!$A$78:$B$89,2,0),"")

D14 of Input >>

=IFERROR(VLOOKUP(Data!$Q$2,Data!$A$92:$B$126,2,0),"")

 

[PatRichard]

Ah - I was overthinking it. That makes more sense. And your formulas are simpler and shorter than what I had. Thank you very much!

 

[Aladin Akyurek]

Ah - I was overthinking it. That makes more sense. And your formulas are simpler and shorter than what I had. Thank you very much!

You are welcome.

 

[monkeyboymike]

You are welcome.

The original poster is sharing his excel file for anyone to use. I downloaded it the other day to use.

I can confirm that this formula is not working correctly:

=IFERROR(VLOOKUP(Data!$M$2+0,Data!$A$39:$B$52,2,0),"")

It only produces a result if the data in the field is a number. Letters are ignored. For example 0 works as expected. However A or B and etc does not.

Any ideas?

Excel version:
Microsoft® Excel® LTSC MSO (16.0.14332.20279) 32-bit

 

[monkeyboymike]

Interestingly, I had ChatGPT help me fix the issue. So its now resolved.