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

KennyA

Board Regular
Joined
Jul 1, 2015
Messages
84
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
any posibility of some more details or a copy of the worksheet?

work for me with some random numbers in the Range C6:D38
 
Last edited:
Upvote 0
The value in C2 is the part description. The part description does show up in C2
B2: =VLOOKUP(C2,C6:D38,2,FALSE) This cell has the #VALUE! error
C3: =IF(D2>0,VLOOKUP(B2,B6:F38,2,FALSE)) This cell has the #VALUE! error A value used in the formula is of the wrong data type
E3: =IF(D2>0,VLOOKUP(B2,B6:F38,4,FALSE)) This cell has the #VALUE! error A value used in the formula is of the wrong data type
F3: =IF(D2>0,VLOOKUP(B2,B6:F38,5,FALSE)) This cell has the #VALUE! error A value used in the formula is of the wrong data type
Columns: B is ID#, C is Description, D is ID#, E is Part Number#, F is Price
Sheet name is BLPVC
This sheet contains the part description, part number and price of 32 parts. The information in the above cells are then being sent to a quote form (Sheet1) as each part and quaantity is selected and added.

I can't figure out why the error is occuring. I have used this exact format and formulas before and they worked fine.
Any help is appreciated.
 
Upvote 0
Aladin,

All text. Here is an example:
2 pumps, Main/Spare Tandem with single inlet, single discharge, 1/2" inlet isolation ball valve, PVC valved Calibration Column, Pump isolation valves, Viton seals, 0-160psi 2.5"pressure gauge and gauge guard, 1/2" Back pressure valve ( 50 psi) and 1/2" discharge

C6 is formatted as text, word wrap is ON.
If you would like me to send you the workdook just let me know how to get it to you.

Thanks
Kenny
 
Last edited:
Upvote 0
C2 has a length > 255 chars. Such yields a #VALUE! error.

Try...

=INDEX(D6:D38,MATCH(TRUE,INDEX(C6:C38= C$2,0),0))

instead.
 
Upvote 0
Thank you Aladin. I will give that a try and let you know how it works out.
Any ideas on the other errors?
B2: =VLOOKUP(C2,C6:D38,2,FALSE) This cell has the #VALUE! error (Maximum chars 4)
C3: =IF(D2>0,VLOOKUP(B2,B6:F38,2,FALSE)) This cell has the #VALUE! error A value used in the formula is of the wrong data type
E3: =IF(D2>0,VLOOKUP(B2,B6:F38,4,FALSE)) This cell has the #VALUE! error A value used in the formula is of the wrong data type (Maximum chars 6)
F3: =IF(D2>0,VLOOKUP(B2,B6:F38,5,FALSE)) This cell has the #VALUE! error A value used in the formula is of the wrong data type (Maximum chars 10)
 
Upvote 0
The other three will all be #Value! error simply because they are looking at B2, which is #Value! Error.

C3: =IF(D2>0,VLOOKUP(B2,B6:F38,2,FALSE))
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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