Need a way to find typo's in an invoice number

[salestemp]

I have two sets of data that would both look like

Store # Ticket # Ticket Date Sales/Returns
5734 5006506307 3/4/2015 -7.49
5666 5003508301 3/24/2015 -7.49
5424 5008408916 3/30/2015 -12.84

After doing a vlookup, I have a number of invoices that come up as missing, however, I know that some of the missing invoices are not actually missing, but are just mistyped. I have played around with fuzzy lookups but this seems to only work on text. My thinking is that the solution would involve formula/VBA that could return a row if say 3 out of the four columns match- if the store #, Date, and Sales are the same, then return that row so I can see the ticket # from that row. I am manually doing pretty much this but I know there has to be some kind of code or formula that will help me. If someone could even point me in the right direction....

[bebo021999]

Could you please share your vlookup formula?

have you tried to convert text to number? =VLOOKUP(--lookup_value,lookup_range,0)

[Richard Buttrey]

Hi,

I'd still be inclined to use a fuzzy search. Microsoft have a free Excel Add-In that I've found useful on several occasions. The way I'd be incliuned to use it with your data would be to create a composite text string for each of your data sets by concatenating all four columns, (or three if you prefer). Then use these two lists of text strings in the fuzzy lookup add-in.

[salestemp]

VLOOKUP formula is
=VLOOKUP(B8,API!A:B,2,FALSE)

I then isolate the rows where the VLOOKUP returns #N/A. This means that the invoice is on the statement (first set of data) but not in my system (second set of data).
I convert the data to numbers before doing the VLOOKUP.

I have tried to convert the numbers into text to do the fuzzy lookup but the fuzzy lookup (even set to a low similarity level) will not recognize that 5009611201 is similar to 5009511201.

So far I've come up with this solution, where I would have to enter each missing invoice manually, and see if the "ALL MATCH" column returns "TRUE"

(This is for one invoice. The results change depending on the invoice I enter)
Amount Date Site All Match Amount and Site Match
F F F FALSE FALSE
T F F FALSE FALSE
F F F FALSE FALSE
F F F FALSE FALSE
F F F FALSE FALSE
F F F FALSE FALSE
T F F FALSE FALSE


I will try concatenating my columns into text with the fuzzy lookup and get back with the results. Thank you!

[salestemp]

=INDEX(API!A:G,MATCH(CONCATENATE(STATEMENT!A23,STATEMENT!D23,STATEMENT!E23),API!G:G,0),1)

Is the solution I came up with which are basically matching concatenations of my 3 columns like Richard suggested. No need for fuzzy lookup even. Thanks!