VLOOKUP #VALUE! error message A value used in the formula is of the wrong data type

[KennyA]

I have tried everything I can think of on this.

The Formula in cell B2 is:
=VLOOKUP(C2,C6:D38,2,FALSE)

The result should only be a number. For example 100 through 138.
I checked and there are no extra spaces before or after the number. The cell is formated as General. I have also tried formatting the cell as a number.

I am using Excel 2013.

Any ideas? Thanks

 

[KennyA]

Aladin,
D2 is the Quantity of the items. It is populated through the user form and works fine.

Jonmo1
Now I understand. Thanks

I will give the solution a try as soon as I can today. I was not aware there was a character limit of 255. Today was not a waste since I learned something!

 

[Aladin Akyurek]

Aladin,
D2 is the Quantity of the items. It is populated through the user form and works fine.

Jonmo1
Now I understand. Thanks

I will give the solution a try as soon as I can today. I was not aware there was a character limit of 255. Today was not a waste since I learned something!

Your C3 is incomplete... Might be better to have:

=IF(D2>0,IF(ISNUMBER(B2),VLOOKUP(B2,B6:F38,2,0),""),"")

The same logic would apply for others.

 

[KennyA]

I just tried Aladins' solution and it works! You guys are awesome!! Thank you so much.

Next is to learn about the INDEX, MATCH and ISNUMBER functions. I have only been working with formulas and macros for a very short time so this is great. So glad that you guys are there to help. Thanks again

 

[Aladin Akyurek]

I just tried Aladins' solution and it works! You guys are awesome!! Thank you so much.

Next is to learn about the INDEX, MATCH and ISNUMBER functions. I have only been working with formulas and macros for a very short time so this is great. So glad that you guys are there to help. Thanks again

You are welcome.