# VLOOKUP Error

#### Kritty

##### New Member
Hi All,

I am having some problems with my VLOOKUP function and cannot seem to figure out what is going wrong.

My excel formula looks a bit like this:

=VLOOKUP (D1, A1:B151,2,FALSE)

The lookup value in cell D1 = 2

I am trying to search column A for the value of "2" and have it return the corresponding value in column B.

Column A contains values from 1.5 to 3 in increments of 0.01 (1.5, 1.51 etc)

Column B contains values from -2 to -0.8 in increments of 0.008 (eg, -2.0, -1.992 etc)

I am encountering a few problems such as:

- If my lookup value is between 1.5 and 1.63 the function works perfectly, but if I pick a higher lookup value (for example 2) it returns #N/A
- If I use TRUE instead of FALSE at the end of my function I will get an approximate match with any look up value I type in between 1.5 and 3. I find this odd as putting "FALSE" at the end of my VLOOKUP will not work for values greater than 1.63?

The values in columns A and B are formulas. I have tried to copy and paste these values separately but I still get the same #N/A problem occurring when I try to search for specific values.

I've also tried making sure all the cells have the same format (general or number etc). Even used the TRIM tool to try and get rid of any invisible spaces that might be there.

Any help would be greatly appreciated as I am definitely stuck going in circles with this one haha

Thanks

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
=sumproduct(--(a1:a151=d1),(b1:b151))

Until works 2,26 then broken, i trying to figure out

Last edited:
yes, you're right there is something odd about that, but setting the vlookup() to true should give you the right answer

Book1
ABCDEF
11.5-21.5-2-2
21.51-1.9921.55-1.96-1.96
31.52-1.9841.6-1.92-1.92
41.53-1.9761.65-1.976-1.976
51.54-1.9681.7-1.936-1.936
61.55-1.961.75-1.896-1.896
71.56-1.9521.8-1.856-1.856
81.57-1.9441.85-1.816-1.816
91.58-1.9361.9-1.776-1.776
101.59-1.9281.95-1.736-1.736
111.6-1.922#N/A-1.696
121.61-1.9122.05#N/A-1.656
131.62-22.1#N/A-1.616
141.63-1.9922.15#N/A-1.576
151.64-1.9842.2#N/A-1.536
161.65-1.9762.25#N/A-1.496
171.66-1.9682.3#N/A-1.456
181.67-1.962.35#N/A-1.416
191.68-1.9522.4#N/A-1.376
201.69-1.9442.45#N/A-1.336
211.7-1.9362.5#N/A-1.296
221.71-1.9282.55#N/A-1.256
231.72-1.922.6#N/A-1.216
241.73-1.9122.65#N/A-1.176
251.74-1.9042.7#N/A-1.136
261.75-1.8962.75#N/A-1.096
271.76-1.8882.8#N/A-1.056
281.77-1.882.85#N/A-1.016
291.78-1.8722.9#N/A-0.976
301.79-1.8642.95#N/A-0.936
311.8-1.8563#N/A-0.896
Sheet3
Cell Formulas
RangeFormula
E1=VLOOKUP(\$D1,A:B,2,0)
F1=VLOOKUP(\$D1,A:B,2)

it's a rounding off issue, this will work

Code:
``=SUMPRODUCT(--(ROUND(\$A\$1:\$A\$151,2)=D1),(\$B\$1:\$B\$151))``

Yes,perfect

Replies
1
Views
1K
Replies
3
Views
245
Replies
3
Views
278
Replies
6
Views
357
Replies
4
Views
343

1,219,671
Messages
6,149,615
Members
450,904
Latest member
Gracifer

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

### Which adblocker are you using?

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

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