vlookup giving false returns

[Watchdawg]

OK, my manager and I are going crazy here. We have the following formula:
=IFERROR(SUM(VLOOKUP(B2,cost8,2,FALSE)),"No Return")

cost8 is the defined name for where to search for the value from B2.

The exact same formula works on another spreadsheet for period 7, however; this one doesn't work in the new period 8 spreadsheet. While the actual data differs, the layouts are exactly the same. Nearly the same amount of data, etc.

Nothing we've done seems to work. It always returns "No Return", even though we can find the data from B2 in the defined list just fine and all the data is there.

What are we doing wrong?

 

[Fluff]

In a blank cell put
=b2=c5
Where c5 is a cell that matches b2, what does the formula return?

 

[Watchdawg]

False

 

[Fluff]

In that case they are not the same value.
What do you have in B2 & the lookup cells?

 

[Watchdawg]

I'm sorry, that was actually returning TRUE. I typo'd the first one
update: I used your formula to lookup the same data from the pivot table (the cost8 defined list) and it returned false.

 

[joeu2004]

First, there is no point in using SUM in this context. =IFERROR(VLOOKUP(B2,cost8,2,FALSE),"No Return") is sufficient.

Second, if B2 should match C5 (per Fluff's example) and the first column of "cost8" is column C and =B2=C5 returns TRUE, I suspect the problem is an infinitesimal difference that arises in the calculation in B2 or C5 or both due to the binary representation of decimal fractions.

In other words, even though Excel might claim =B2=C5 is TRUE, perhaps =B2-C5=0 is FALSE, notwithstanding that they might be appear equal.

(The inconsistent results are due to dubious Excel design decisions. I can explain further, if you wish.)

The remedy is to ensure that both B2 and the first column of "cost8" are explicitly rounded to the precision that you expect to be accurate.

Perhaps VLOOKUP(ROUND(B2,2), cost8, 2, FALSE) is sufficient. But it would be better to put the explicit rounding in the formula in B2.

If not, the problem is with unrounded values in the first column of "cost8". Put the explicit rounding (ROUND) there.

(Note: It is not necessary to explicitly round constants. That is 1.23 is sufficient; ROUND(1.23,2) is unnecessary.)

If I'm correct about different results for =B2=C5 and =B2-C5=0 and you need further assistance with explicit rounding, upload an example Excel file (redacted) that demonstrates the problem to a file-sharing website (e.g. box.net/files), and post the public/save URL in a response here. Test the download URL first, being careful to log out of the file-sharing website. (If you use box.net/files, ignore any preview errors, and just download the file.)

Caveat: Some particants object to downloading example Excel files. But in this case, the devil might be in the details that cannot be demonstrated by posting displayed values.