Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VLOOKUP will not return a value

0

I am having a problem with VLOOKUP.  This formula (    =IFERROR(VLOOKUP(J4,D$4:D$34,1,FALSE),"NO")     or   =IFERROR(VLOOKUP(D4,J$4:J$34,1,TRUE),"NO")        ) will not return  the value ($2,522.66) unless  the lookup value(J4) is typed in rather than derived from the formula (  =IF(L4<>"",I4+L4,""    ).  The strange thing is that the it works correctly for some numbers in the column!  What am I doing wrong??

I am running Office 365

Answer
Discuss

Discussion

Thank you! This solves my problem. I don't know why I didn't think of it!!
TWHEARD (rep: 4) Dec 13, '20 at 8:43 pm
@TWHEARD I think this is rather an important point for many forum visitors who would benefit from your marking my answer as Selected. Thank you.
Variatus (rep: 4889) Dec 14, '20 at 2:55 am
Add to Discussion

Answers

0
Selected Answer

In your workbook you use the custom cell format "$"#,##0.00 which, by the way, has an unnecessarily complicated syntax because the $ sign is accepted natively. $#,##0.00 would accomplish the same thing. But what, exactly, is it that it accomplishes?

In simple terms, it will truncate displayed decimal digits, rounding the last one. If the result of a calculation is 123.33333 the format will force the display of 123.33. But please bear in mind that the cell value remains at 123.33333 and VLOOKUP will not find 123.33 even if it's right there in front of you to see and touch.

Therefore, in order to apply VLOOKUP (or MATCH, by the way) to this task you need to ensure that both the look-up column and the look-up value has 123.33. Use the ROUND() function

=ROUND(37/3,2)

But it isn't as simple as that because in your example D4=2522.66 has no hidden digits. I4 =2499.53 and L4 =73.13 have no hidden digits either. But =VLOOKUP(I4+L4,D4:D30,1,FALSE) returns #N/A. This has to do with the way Excel's Double data type  handles integers. The calculation I4+L4 needs the Double data type and therefore digits are added. Enter this formula in A1 

=333.33-333.3

. The cell shows a value of 0.03. Now question this result with

 =(A1-0.03)=0

The surprising answer is FALSE and that is what's been baffling you.

When handling even mildly complicated numbers Excel adds insignificant decimals. Under normal circumstances you don't get to see them because they are hidden away by cell formatting and too small to make a rounding difference. (Yes, they can add up to a rounding difference when thousands of values are summed up.)  Try this formula to see what we're talking about, =333.33-333.3-0.03 (the result is not 0). The solution is easy. The formula below returns TRUE.

=ROUND(J5-0.03,2)=0

And that's the cure for your LOOKUP problem, too.

Discuss
0

6 most common reasons why your VLOOKUP is not working.

  1. You Need an Exact Match
  2. Lock the Table Reference
  3. A Column Has Been Inserted
  4. The Table has got Bigger
  5. VLOOKUP Cannot Look to its Left
  6. Your Table Contains Duplicates

You can get more excel tips from https://www.o365cloudexperts.com

Jerry

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login