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

pcorpz

pcorpz

Active Member
Joined
Oct 29, 2004
Messages
324
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)
 
Last edited:

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
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.
 
Upvote 0
apologies CyrusTheVirus! here it is:

mrexcelscreenshot.JPG

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?
 
Last edited:
Upvote 0
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??
 
Last edited:
Upvote 0
CyrusTheVirus said:
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??
Click to expand...

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?
 
Upvote 0
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?
Click to expand...

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.
 
Upvote 0
CyrusTheVirus said:
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.
Click to expand...

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!
 
Upvote 0
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!
Click to expand...

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.
Click to expand...
 
Upvote 0
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.

Helper 1Helper 2Helper 3Helper 4UsernameQuantityDateItem No.Type Of Match?Adj. PriceHelper 1Helper 2Helper 3Helper 4UsernameQuantityDateItem No.Adj. Price
Andrea241436549Andrea24143654Andrea241AndreaAndrea2417/8/20199Approximate Match $ 1,461.00Andrea73436602Andrea7343660Andrea73AndreaAndrea737/14/20192 $ 2,156.00
Andrea465436595Andrea46543659Andrea465AndreaAndrea4657/13/20195Approximate Match $ 2,034.00Andrea4654365880Andrea46543658Andrea465AndreaAndrea4657/12/201980 $ 2,034.00
Andrea73436602Andrea7343660Andrea73AndreaAndrea737/14/20192Exact Match $ 2,156.00Andrea2414365412Andrea24143654Andrea241AndreaAndrea2417/8/201912 $ 1,461.00
Brian654365610Brian6543656Brian65BrianBrian657/10/201910No Match Nothing FoundAndrea8043657646Andrea8043657Andrea80AndreaAndrea807/11/2019646 $ 4,544.00
Brian109436576Brian10943657Brian109BrianBrian1097/11/20196No Match Nothing FoundAndrea454365718Andrea4543657Andrea45AndreaAndrea457/11/201918 $ 1,109.00
Brian4794366210Brian47943662Brian479BrianBrian4797/16/201910No Match Nothing FoundKaren104365960Karen1043659Karen10KarenKaren107/13/201960 $ 1,683.00
Karen3454364810Karen34543648Karen345KarenKaren3457/2/201910Approximate Match $ 1,683.00Karen373436524Karen37343652Karen373KarenKaren3737/6/20194 $ 4,069.00
Karen10436509Karen1043650Karen10KarenKaren107/4/20199Approximate Match $ 1,683.00Karen904365948Karen9043659Karen90KarenKaren907/13/201948 $ 3,276.00
Karen373436515Karen37343651Karen373KarenKaren3737/5/20195Approximate Match $ 4,069.00Karen484367448Karen4843674Karen48KarenKaren487/28/201948 $ 1,255.00
Karen89436524Karen8943652Karen89KarenKaren897/6/20194Exact Match $ 3,529.00Karen89436474Karen8943647Karen89KarenKaren897/1/20194 $ 4,205.00
Paul59436476Paul5943647Paul59PaulPaul597/1/20196Approximate Match $ 2,158.00Karen89436524Karen8943652Karen89KarenKaren897/6/20194 $ 3,529.00
Paul73436495Paul7343649Paul73PaulPaul737/3/20195Approximate Match $ 4,418.00Paul504364884Paul5043648Paul50PaulPaul507/2/201984 $ 3,730.00
Steven416436555Steven41643655Steven416StevenSteven4167/9/20195No Match Nothing FoundPaul734364745Paul7343647Paul73PaulPaul737/1/201945 $ 4,418.00
Steven196436581Steven19643658Steven196StevenSteven1967/12/20191No Match Nothing FoundPaul104365745Paul1043657Paul10PaulPaul107/11/201945 $ 571.00
Steven9436619Steven943661Steven9StevenSteven97/15/20199No Match Nothing FoundPaul104365948Paul1043659Paul10PaulPaul107/13/201948 $ 2,158.00
Tony211436537Tony21143653Tony211TonyTony2117/7/20197No Match Nothing FoundPaul594364954Paul5943649Paul59PaulPaul597/3/201954 $ 2,158.00

<tbody>
</tbody>



A2 (copied down and over to column D whatever):
Code: 
=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):
Code: 
=IF(ISNUMBER(MATCH([@[Helper 1]],Table2[Helper 1],0)),"Exact Match",IF(ISNUMBER([@[Adj. Price]]),"Approximate Match","No Match"))

J2 (copied down):
Code: 
=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):
Code: 
=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])))
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Sumproduct with 3 tables?
Replies
4
Views
215
killerstein
K
H
  • Question
Create a ticket sales management system
Replies
1
Views
280
Harvey12
H
D
  • Question
What formula do i need vlookup to index match or something else?
Replies
8
Views
316
Peter_SSs
Peter_SSs
O
  • Question
Vlookup() and Index(Match()) returning N/A, even though the lookup value exists in the range and is identical to that value within the range.
Replies
6
Views
1K
nemmi69
nemmi69
A
  • Question
Lookup and match part lines to a purchase order number
Replies
1
Views
319
ac7
A

Forum statistics

Threads
1,215,043
Messages
6,122,825
Members
449,096
Latest member
Erald

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top