VLOOKUP Error

Kritty

New Member
Joined
Feb 18, 2019
Messages
4
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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
=sumproduct(--(a1:a151=d1),(b1:b151))

Until works 2,26 then broken, i trying to figure out
 
Last edited:
Upvote 0
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)
 
Upvote 0
it's a rounding off issue, this will work

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

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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