vlookup, if no exact match found, do fuzzy lookup? formula or macro

[pcorpz]

I have two tables, sales order and purchase order. The only way I know how to do a vlookup is to create a helper column to use with my lookup (username&date&qty&itemno). 90% finds a match, but for the 10% either, the username or date (it could be 1 day after or 1 month) or qty is different.. what's the best way to return the closest match if no exact match is found? I tried doing wildcard vlookup (&"*") but I got the same result.. any help would be appreciated!

Here's my formula:
=IFERROR(VLOOKUP([@Lookup]&"*",PO!R:U,4,FALSE),0)

 

[CyrusTheVirus]

Can you please provide an example of your data set and your expected results?

It's almost always best to provide an example (both the data set, and your expected results) so users can provide you with what you are expecting.

 

[pcorpz]

apologies CyrusTheVirus! here it is:

https://www.dropbox.com/s/nwsjby5ya531eop/mrexcelscreenshot.JPG?dl=0
My end goal is to find the closest match of the ones that didn't find the exact match. The only way I know how to do a lookup is by vlookup -- and in my lookup column, I concatenated several columns (date, username,quantity and item no). 90% has an exact match in my PO table, the 10% that didn't find the match, I would like excel to go through the ones that didn't find a match, and capture the closest one on the list. Usually the date is a week after in the PO table, or the qty or username is different... what is the best way to do this?

 

[CyrusTheVirus]

Thanks.

One of the problems with doing things like this is that your results CAN BE EXTREMELY VOLATILE AND INCONSISTENT. For instance, the date might match, but the rest might not, or the date might not match, but the rest might, therefore you'd pick the one with the correct date (assuming you'd be analyzing from the beginning of the cell to the end), even though the other cell could potentially have more correct data.

What is the reason you are needing to do this? Are you sure a 90% match lookup is really what you are wanting??

 

[pcorpz]

Thanks.

One of the problems with doing things like this is that your results CAN BE EXTREMELY VOLATILE AND INCONSISTENT. For instance, the date might match, but the rest might not, or the date might not match, but the rest might, therefore you'd pick the one with the correct date (assuming you'd be analyzing from the beginning of the cell to the end), even though the other cell could potentially have more correct data.

What is the reason you are needing to do this? Are you sure a 90% match lookup is really what you are wanting??

Here's my scenario. I have two tables, Sales Order and Purchase Order. I want to be able to show the PO cost of the item in the Sales Order. I don't have a reference field to link the two, so in order for me to do a lookup, I concatenated columns to make a unique record on both tables (I combined the date, username, qty and itemcode). 90% of the items will have an exact match which is great. What can I do for the 10% that do not have an exact match to give me the closest match? Itemcode will always be the same, but dates, username and qty will be different. One way I can think of is to limit the lookup list to only the ones that did not return a value (po cost)...does that make sense?

 

[CyrusTheVirus]

One way I can think of is to limit the lookup list to only the ones that did not return a value (po cost)...does that make sense?

I would do what you mentioned above ^^^

Because I am not seeing the value of trying to pull the most accurate. But... if you are adamant in finding approximate matches, then I might be able to assist, and I'm sure other users could, but first you need to answer the following questions.

1) What is the benefit to you of finding approximate matches?

2) Is it better to isolate the rows that don't have a match and then investigate why there is no match, instead of finding approximate matches? I'd say yes.

If you came up with a reason to question 1, and after answering question 2 you still think approximate match is the best way to achieve what you are trying to achieve, then go to question 3.

3) What is the order of significance of the match? Is name more important than date, is date more important than name, is quantity more important than name etc. etc. etc. B/c you first need to determine which criteria is the most significant and then take it from there.

Reach out with questions.

 

[pcorpz]

I would do what you mentioned above ^^^

Because I am not seeing the value of trying to pull the most accurate. But... if you are adamant in finding approximate matches, then I might be able to assist, and I'm sure other users could, but first you need to answer the following questions.

1) What is the benefit to you of finding approximate matches?

2) Is it better to isolate the rows that don't have a match and then investigate why there is no match, instead of finding approximate matches? I'd say yes.

If you came up with a reason to question 1, and after answering question 2 you still think approximate match is the best way to achieve what you are trying to achieve, then go to question 3.

3) What is the order of significance of the match? Is name more important than date, is date more important than name, is quantity more important than name etc. etc. etc. B/c you first need to determine which criteria is the most significant and then take it from there.

Reach out with questions.

Hi Cyrus, failed to mention that yes, there will be instances where they don't exactly match... for date differences, it could be because the user posted the PO a day later.. or a week or sometimes even a month later... for user - it could be because another user entered the PO.. and for qty - it could be because it's a partial purchase order.. I think out of the 3 criteria, the possibility of date and qty difference would happen more than a different user.. hope this helps!

 

[CyrusTheVirus]

Thanks pcorpz.

I think out of the 3 criteria, the possibility of date and qty difference would happen more than a different user.. hope this helps!

But what is the most significant match criteria?

3) What is the order of significance of the match? Is name more important than date, is date more important than name, is quantity more important than name etc. etc. etc. B/c you first need to determine which criteria is the most significant and then take it from there.

 

[pcorpz]

Thanks pcorpz.



But what is the most significant match criteria?

Sorry here's the order! 1. Username 2. Quantity 3. Date

 

[CyrusTheVirus]

Ok, you could try something like the below. Copy/paste the below table into excel (name left table 'Table1' and right table 'Table2', and then copy/paste the formulas as mentioned. Reach out with any questions. See if this might work for you. Please test before using, and please note that doing it this way can be (and probably will be) quite volatile and inconsistent. Hope this helps.

[TABLE="width: 1805"]
<tbody>[TR]
[TD]Helper 1[/TD]
[TD]Helper 2[/TD]
[TD]Helper 3[/TD]
[TD]Helper 4[/TD]
[TD]Username[/TD]
[TD]Quantity[/TD]
[TD]Date[/TD]
[TD]Item No.[/TD]
[TD]Type Of Match?[/TD]
[TD]Adj. Price[/TD]
[TD][/TD]
[TD]Helper 1[/TD]
[TD]Helper 2[/TD]
[TD]Helper 3[/TD]
[TD]Helper 4[/TD]
[TD]Username[/TD]
[TD]Quantity[/TD]
[TD]Date[/TD]
[TD]Item No.[/TD]
[TD]Adj. Price[/TD]
[/TR]
[TR]
[TD]Andrea241436549[/TD]
[TD]Andrea24143654[/TD]
[TD]Andrea241[/TD]
[TD]Andrea[/TD]
[TD]Andrea[/TD]
[TD="align: right"]241[/TD]
[TD="align: right"]7/8/2019[/TD]
[TD="align: right"]9[/TD]
[TD]Approximate Match[/TD]
[TD] $ 1,461.00[/TD]
[TD][/TD]
[TD]Andrea73436602[/TD]
[TD]Andrea7343660[/TD]
[TD]Andrea73[/TD]
[TD]Andrea[/TD]
[TD]Andrea[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]7/14/2019[/TD]
[TD="align: right"]2[/TD]
[TD] $ 2,156.00[/TD]
[/TR]
[TR]
[TD]Andrea465436595[/TD]
[TD]Andrea46543659[/TD]
[TD]Andrea465[/TD]
[TD]Andrea[/TD]
[TD]Andrea[/TD]
[TD="align: right"]465[/TD]
[TD="align: right"]7/13/2019[/TD]
[TD="align: right"]5[/TD]
[TD]Approximate Match[/TD]
[TD] $ 2,034.00[/TD]
[TD][/TD]
[TD]Andrea4654365880[/TD]
[TD]Andrea46543658[/TD]
[TD]Andrea465[/TD]
[TD]Andrea[/TD]
[TD]Andrea[/TD]
[TD="align: right"]465[/TD]
[TD="align: right"]7/12/2019[/TD]
[TD="align: right"]80[/TD]
[TD] $ 2,034.00[/TD]
[/TR]
[TR]
[TD]Andrea73436602[/TD]
[TD]Andrea7343660[/TD]
[TD]Andrea73[/TD]
[TD]Andrea[/TD]
[TD]Andrea[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]7/14/2019[/TD]
[TD="align: right"]2[/TD]
[TD]Exact Match[/TD]
[TD] $ 2,156.00[/TD]
[TD][/TD]
[TD]Andrea2414365412[/TD]
[TD]Andrea24143654[/TD]
[TD]Andrea241[/TD]
[TD]Andrea[/TD]
[TD]Andrea[/TD]
[TD="align: right"]241[/TD]
[TD="align: right"]7/8/2019[/TD]
[TD="align: right"]12[/TD]
[TD] $ 1,461.00[/TD]
[/TR]
[TR]
[TD]Brian654365610[/TD]
[TD]Brian6543656[/TD]
[TD]Brian65[/TD]
[TD]Brian[/TD]
[TD]Brian[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]7/10/2019[/TD]
[TD="align: right"]10[/TD]
[TD]No Match[/TD]
[TD] Nothing Found[/TD]
[TD][/TD]
[TD]Andrea8043657646[/TD]
[TD]Andrea8043657[/TD]
[TD]Andrea80[/TD]
[TD]Andrea[/TD]
[TD]Andrea[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]7/11/2019[/TD]
[TD="align: right"]646[/TD]
[TD] $ 4,544.00[/TD]
[/TR]
[TR]
[TD]Brian109436576[/TD]
[TD]Brian10943657[/TD]
[TD]Brian109[/TD]
[TD]Brian[/TD]
[TD]Brian[/TD]
[TD="align: right"]109[/TD]
[TD="align: right"]7/11/2019[/TD]
[TD="align: right"]6[/TD]
[TD]No Match[/TD]
[TD] Nothing Found[/TD]
[TD][/TD]
[TD]Andrea454365718[/TD]
[TD]Andrea4543657[/TD]
[TD]Andrea45[/TD]
[TD]Andrea[/TD]
[TD]Andrea[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]7/11/2019[/TD]
[TD="align: right"]18[/TD]
[TD] $ 1,109.00[/TD]
[/TR]
[TR]
[TD]Brian4794366210[/TD]
[TD]Brian47943662[/TD]
[TD]Brian479[/TD]
[TD]Brian[/TD]
[TD]Brian[/TD]
[TD="align: right"]479[/TD]
[TD="align: right"]7/16/2019[/TD]
[TD="align: right"]10[/TD]
[TD]No Match[/TD]
[TD] Nothing Found[/TD]
[TD][/TD]
[TD]Karen104365960[/TD]
[TD]Karen1043659[/TD]
[TD]Karen10[/TD]
[TD]Karen[/TD]
[TD]Karen[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7/13/2019[/TD]
[TD="align: right"]60[/TD]
[TD] $ 1,683.00[/TD]
[/TR]
[TR]
[TD]Karen3454364810[/TD]
[TD]Karen34543648[/TD]
[TD]Karen345[/TD]
[TD]Karen[/TD]
[TD]Karen[/TD]
[TD="align: right"]345[/TD]
[TD="align: right"]7/2/2019[/TD]
[TD="align: right"]10[/TD]
[TD]Approximate Match[/TD]
[TD] $ 1,683.00[/TD]
[TD][/TD]
[TD]Karen373436524[/TD]
[TD]Karen37343652[/TD]
[TD]Karen373[/TD]
[TD]Karen[/TD]
[TD]Karen[/TD]
[TD="align: right"]373[/TD]
[TD="align: right"]7/6/2019[/TD]
[TD="align: right"]4[/TD]
[TD] $ 4,069.00[/TD]
[/TR]
[TR]
[TD]Karen10436509[/TD]
[TD]Karen1043650[/TD]
[TD]Karen10[/TD]
[TD]Karen[/TD]
[TD]Karen[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7/4/2019[/TD]
[TD="align: right"]9[/TD]
[TD]Approximate Match[/TD]
[TD] $ 1,683.00[/TD]
[TD][/TD]
[TD]Karen904365948[/TD]
[TD]Karen9043659[/TD]
[TD]Karen90[/TD]
[TD]Karen[/TD]
[TD]Karen[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]7/13/2019[/TD]
[TD="align: right"]48[/TD]
[TD] $ 3,276.00[/TD]
[/TR]
[TR]
[TD]Karen373436515[/TD]
[TD]Karen37343651[/TD]
[TD]Karen373[/TD]
[TD]Karen[/TD]
[TD]Karen[/TD]
[TD="align: right"]373[/TD]
[TD="align: right"]7/5/2019[/TD]
[TD="align: right"]5[/TD]
[TD]Approximate Match[/TD]
[TD] $ 4,069.00[/TD]
[TD][/TD]
[TD]Karen484367448[/TD]
[TD]Karen4843674[/TD]
[TD]Karen48[/TD]
[TD]Karen[/TD]
[TD]Karen[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]7/28/2019[/TD]
[TD="align: right"]48[/TD]
[TD] $ 1,255.00[/TD]
[/TR]
[TR]
[TD]Karen89436524[/TD]
[TD]Karen8943652[/TD]
[TD]Karen89[/TD]
[TD]Karen[/TD]
[TD]Karen[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]7/6/2019[/TD]
[TD="align: right"]4[/TD]
[TD]Exact Match[/TD]
[TD] $ 3,529.00[/TD]
[TD][/TD]
[TD]Karen89436474[/TD]
[TD]Karen8943647[/TD]
[TD]Karen89[/TD]
[TD]Karen[/TD]
[TD]Karen[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]4[/TD]
[TD] $ 4,205.00[/TD]
[/TR]
[TR]
[TD]Paul59436476[/TD]
[TD]Paul5943647[/TD]
[TD]Paul59[/TD]
[TD]Paul[/TD]
[TD]Paul[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]6[/TD]
[TD]Approximate Match[/TD]
[TD] $ 2,158.00[/TD]
[TD][/TD]
[TD]Karen89436524[/TD]
[TD]Karen8943652[/TD]
[TD]Karen89[/TD]
[TD]Karen[/TD]
[TD]Karen[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]7/6/2019[/TD]
[TD="align: right"]4[/TD]
[TD] $ 3,529.00[/TD]
[/TR]
[TR]
[TD]Paul73436495[/TD]
[TD]Paul7343649[/TD]
[TD]Paul73[/TD]
[TD]Paul[/TD]
[TD]Paul[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]7/3/2019[/TD]
[TD="align: right"]5[/TD]
[TD]Approximate Match[/TD]
[TD] $ 4,418.00[/TD]
[TD][/TD]
[TD]Paul504364884[/TD]
[TD]Paul5043648[/TD]
[TD]Paul50[/TD]
[TD]Paul[/TD]
[TD]Paul[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]7/2/2019[/TD]
[TD="align: right"]84[/TD]
[TD] $ 3,730.00[/TD]
[/TR]
[TR]
[TD]Steven416436555[/TD]
[TD]Steven41643655[/TD]
[TD]Steven416[/TD]
[TD]Steven[/TD]
[TD]Steven[/TD]
[TD="align: right"]416[/TD]
[TD="align: right"]7/9/2019[/TD]
[TD="align: right"]5[/TD]
[TD]No Match[/TD]
[TD] Nothing Found[/TD]
[TD][/TD]
[TD]Paul734364745[/TD]
[TD]Paul7343647[/TD]
[TD]Paul73[/TD]
[TD]Paul[/TD]
[TD]Paul[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]7/1/2019[/TD]
[TD="align: right"]45[/TD]
[TD] $ 4,418.00[/TD]
[/TR]
[TR]
[TD]Steven196436581[/TD]
[TD]Steven19643658[/TD]
[TD]Steven196[/TD]
[TD]Steven[/TD]
[TD]Steven[/TD]
[TD="align: right"]196[/TD]
[TD="align: right"]7/12/2019[/TD]
[TD="align: right"]1[/TD]
[TD]No Match[/TD]
[TD] Nothing Found[/TD]
[TD][/TD]
[TD]Paul104365745[/TD]
[TD]Paul1043657[/TD]
[TD]Paul10[/TD]
[TD]Paul[/TD]
[TD]Paul[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7/11/2019[/TD]
[TD="align: right"]45[/TD]
[TD] $ 571.00[/TD]
[/TR]
[TR]
[TD]Steven9436619[/TD]
[TD]Steven943661[/TD]
[TD]Steven9[/TD]
[TD]Steven[/TD]
[TD]Steven[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]7/15/2019[/TD]
[TD="align: right"]9[/TD]
[TD]No Match[/TD]
[TD] Nothing Found[/TD]
[TD][/TD]
[TD]Paul104365948[/TD]
[TD]Paul1043659[/TD]
[TD]Paul10[/TD]
[TD]Paul[/TD]
[TD]Paul[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7/13/2019[/TD]
[TD="align: right"]48[/TD]
[TD] $ 2,158.00[/TD]
[/TR]
[TR]
[TD]Tony211436537[/TD]
[TD]Tony21143653[/TD]
[TD]Tony211[/TD]
[TD]Tony[/TD]
[TD]Tony[/TD]
[TD="align: right"]211[/TD]
[TD="align: right"]7/7/2019[/TD]
[TD="align: right"]7[/TD]
[TD]No Match[/TD]
[TD] Nothing Found[/TD]
[TD][/TD]
[TD]Paul594364954[/TD]
[TD]Paul5943649[/TD]
[TD]Paul59[/TD]
[TD]Paul[/TD]
[TD]Paul[/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]7/3/2019[/TD]
[TD="align: right"]54[/TD]
[TD] $ 2,158.00[/TD]
[/TR]
</tbody>[/TABLE]



A2 (copied down and over to column D whatever):

=IF(COLUMNS($A2:A2)=1,[@Username]&[@Quantity]&[@Date]&[@[Item No.]],IF(COLUMNS($A2:A2)=2,[@Username]&[@Quantity]&[@Date],IF(COLUMNS($A2:A2)=3,[@Username]&[@Quantity],[@Username])))

I2 (copied down):

=IF(ISNUMBER(MATCH([@[Helper 1]],Table2[Helper 1],0)),"Exact Match",IF(ISNUMBER([@[Adj. Price]]),"Approximate Match","No Match"))

J2 (copied down):

=IFERROR(IFERROR(IFERROR(IFERROR(INDEX(Table2[Adj. Price],MATCH([@[Helper 1]],Table2[Helper 1],0)),INDEX(Table2[Adj. Price],MATCH([@[Helper 2]],Table2[Helper 2],0))),INDEX(Table2[Adj. Price],MATCH([@[Helper 3]],Table2[Helper 3],0))),INDEX(Table2[Adj. Price],MATCH([@[Helper 4]],Table2[Helper 4],0))),"Nothing Found")

L2 (copied down and over to column O whatever):

=IF(COLUMNS($L2:L2)=1,[@Username]&[@Quantity]&[@Date]&[@[Item No.]],IF(COLUMNS($L2:L2)=2,[@Username]&[@Quantity]&[@Date],IF(COLUMNS($L2:L2)=3,[@Username]&[@Quantity],[@Username])))