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)

 

[pcorpz]

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])))

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

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

=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