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:
Sort by date Sort by votes
CyrusTheVirus said:
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])))
Click to expand...



Thanks Cyrus, super appreciate you helping me! I applied your formula (and I think I got it to work) -- also tried to understand it -- what's the use of these two formulas?
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])))
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])))
[/QUOTE]

Also for the approximate matches -- it's not returning the closest match -- for the helper columns -- i noticed you left out the item no's in helper 2, 3, 4 -- could that be affected by that? the item no should always be included in the lookup....ive attached my screenshot

https://www.dropbox.com/s/ctmh4tc839q97ot/mrexcelscreenshot1.JPG
 
Last edited:
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
You must log in or register to reply here.

Similar threads

K
  • Solved
Sumproduct with 3 tables?
Replies
4
Views
213
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
315
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,214,911
Messages
6,122,198
Members
449,072
Latest member
DW Draft

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