VLOOKUP returning incorrect values for unfound match

[scaffdog845]

Good afternoon all,

I'm trying to help a colleague automate a daily process he must perform in Excel. I had suspected it to be a simple job for a VLOOKUP formula, however incorrect values are being returned for items without a match.

In the attached example the 'Final! sheet is where he would have to manually enter amounts from an SAP printout into C:C for jobs matching job numbers in B:B. The sheet 'Source Data! is a striped down example of an SAP data dump where he retrieves his information.

The VLOOKUP formula I added to D:D has a couple of issues I could use a hand with.

1. When a value from 'Final!B:B is not found in 'Source Data!B:B, the formula appears to return the value from the previous item.

2. Some values in 'Final!B:B have either an "-s" or "-ss" appended to what needs to be matched in 'Source Data!B:B. I believe limiting the lookup to the first 8 characters starting from the left would suffice.

Any suggestions?

As always.... thamks for the help!

[contaminated]

Try to add 0 or FALSE into the fourth arqument of VLOOKUP formula

[NBVC]

Perhaps?

=IF(ISNA(VLOOKUP(SUBSTITUTE(SUBSTITUTE(B2,"-ss",""),"-s",""),SAPCostDump,106,0)),"",VLOOKUP(SUBSTITUTE(SUBSTITUTE(B2,"-ss",""),"-s",""),SAPCostDump,106,0))

or in 2007

=IFERROR(VLOOKUP(SUBSTITUTE(SUBSTITUTE(B2,"-ss",""),"-s",""),SAPCostDump,106,0),0)

[Weezy]

So if i understand this correctly. you want an exact match from column B on "Final" to match column B on "Source Data" and return the value in column 106 (DC). your formula is almost correct to do that. you simply need to change the formula in Cell D2 from

=VLOOKUP(B2,SAPCostDump,106)

to

=VLOOKUP(B2,SAPCostDump,106,false)

and copy it down. However, when you do this you will get a lot of noise from errors when nothing matches at all. to combat that you can use this formula in cell D2 and copy it down instead.

=IF(ISERROR(VLOOKUP(B2,SAPCostDump,106,FALSE)),"",VLOOKUP(B2,SAPCostDump,106,FALSE))

[contaminated]

Independent from excel version formulae

=LOOKUP(99^99,CHOOSE({1,2},0,VLOOKUP(SUBSTITUTE(SUBSTITUTE(B2,"-ss",""),"-s",""),SAPCostDump,106,0)))

HTH

[scaffdog845]

All great suggestions guys. Thanks for the help. I went with NBVC on this one as his solution helped by stripping the "-s" and "-ss" from the job names before looking for the match as well as eliminating the issue of phantom results.

judging from the avatars I would say we have some world cup excel guru fans out there! Enjoy the rest of the matches.

THANKS