vLOOKUP Issues

**dsciola** · 06-18-2013, 08:13 PM

I am trying to run a vLOOKUP formula across 2 tabs to pull relevant inventory date information. For the majority of the formula's / inventory date items, the formula works fine. However, some of them are not pulling the correct date. I do not know why, only noticed by spot-checking a few cells.

Was hoping I could get some help on this. Is my formula wrong? Should there be an exact text instead of a number or something else?

Please see attached spreadsheet...specifically, rows 141, 906, and 916 on the 'Dates' tab, and also the respectively accompanying rows 140, 275, and 285 on the 'Output' tab. let me know if you have any questions.

Thanks

Dom

**Pete_UK** · 06-18-2013, 08:47 PM

Most of your Unique Identifiers in the Dates sheet have spaces after them, so it is difficult to get an exact match, especially as some of them are proper numbers.

You can try this in G4 of your dates sheet:

=IFERROR(VLOOKUP(A4&"*",Dates!B:G,6,0),IFERROR(VLOOKUP(A4,Dates!B:G,6),""))

which will rectify those particular problems, as it will carry out an exact match and then an inexact match.

Hope this helps.

Pete

**dsciola** · 06-19-2013, 05:44 PM

I see, thanks Pete! Formula works great. Never knew one small space could be such a headache either.

If you dont mind me asking, I had a few questions concerning the above formula:

1 - Why do you have to use an 'IFERROR' formula here and why then must it be used twice? Is this just to help the user see if formula is wrong?
2 - What does the "*" do? Not familiar with that formula syntax or whatever it is exactly.
3 - Is the "0" following the "6" to find an exact match? Please correct me if I am wrong.

I also placed the formula in the 'Output' tab and it worked just as well. Thanks again for your time and assistance.

Dom

**Pete_UK** · 06-19-2013, 06:32 PM

Hi Dom,

glad to hear that it worked for you. In answer to your questions:

1. When I was testing it, I accidentally put a comma after the 6 in the second VLOOKUP (which actually made it virtually the same as first), and that started to produce errors, so before I spotted the comma I wrapped a second IFERROR around that part. I saw and removed the comma while posting my response to you, and forgot to remove the inner IFERROR, so you could probably achieve the same thing by doing this (not tested):

=IFERROR(VLOOKUP(A4&"*",Dates!B:G,6,0),VLOOKUP(A4,Dates!B:G,6))

2. The asterisk is the wildcard character, meaning any number of characters, so the first VLOOKUP is trying to match with textual numbers which begin with the value in A4 (also a textual number) and any number of other characters (i.e. spaces).

3. Yes, putting a zero as the 4th parameter in VLOOKUP means to carry out an exact match.

Hope this helps.

Pete