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.

[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):
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
You must log in or register to reply here.

Similar threads

W
  • Question Question
Index/Match Duplicate Values With No Helper Cells
Replies
3
Views
287
Domenic
D
J
  • Question Question
Multiple Layer VLOOKUP, or better alternative?
Replies
9
Views
596
JukeExcel
J
S
  • Question Question
Vlookuping matching values with hidden rounding errors
Replies
5
Views
488
awoohaw
awoohaw
T
  • Solved
How to return comma separated data (already set up) and return data into separately (matched) cells
Replies
3
Views
119
Fluff
Fluff
B
  • Solved
VLOOKUP issue with wildcard parameter for partial matches in number/letter combinations
2
Replies
10
Views
839
BrooksProctor
B

Forum statistics

Threads
1,223,723
Messages
6,174,115
Members
452,545
Latest member
boybenqn

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