Forum Discussion
Solved
Fuzzy lookup multiple criteria
Hi, First entry for a sales record is 26,695 rupees. Later, when I receive the money, I record a receipt entry for the whole amount (as in sales) or a smaller, roughly equivalent amount. Bu...
- there are a few options. I just copy useful formulas to a single workbook so I know where to find them.
a) no file attached - if it won't let you then try using online sharing like OneDrive, SharePoint or the like
b) there are lots of ways to do approximate matches but they need more criteria. For example I have one that will do the best match of the first x number of characters and return the answer with the most characters matched. That could be tweaked to remove spaces to help in this case
c) You say "First entry for a sales record is ... Later, when I receive the money, I record a receipt entry" and then you give an example that the correct match in that example based on amount and date is a receipt BEFORE the sales record.
b) there are lots of ways to do approximate matches but they need more criteria. For example I have one that will do the best match of the first x number of characters and return the answer with the most characters matched. That could be tweaked to remove spaces to help in this case
c) You say "First entry for a sales record is ... Later, when I receive the money, I record a receipt entry" and then you give an example that the correct match in that example based on amount and date is a receipt BEFORE the sales record.
I have attached the requisite file.
I'm trying to say that the sale entry occurs first. Take May 5 as an example; however, I will receive payment (a receipt entry) on May 11. It's possible that the amount I receive will be the same as or even less than the sale price. because stock loss may cause the debtor to forfeit some money. In any case, it will be a single payment rather than several smaller ones.
Second, parties with the same name will exist. Date and sum, however, will be the requirements to match.
g_avinash But my point is that in your example:
the highlighted desired Output is a RECEIPT that happens on 28-04-2023 which is BEFORE the sales item you were searching for which happened on 01-05-2023. Was this a bad example or is the explanation incorrect?
As for the NearMatch I have attached your workbook with it added BELOW (note I edited this post and remove the file to prevent confusion). Here is an example how it works:
notice how it found the "ve" and then "smo" in "wave smooth". It would find that even if the text in C11 was "vesmoxyz123" since that was the 'best' match of all the options. you can also add the min_match parameter to say it must match at least X characters otherwise ignore the results. Play with it and see if that helps.
Many thanks, sir. I'll definitely give it a shot. Hope this meet the two requirements of matching with Date and Amount. I apologise for the file that is attached regarding the example.
g_avinash So I forgot to check / use your file so I'll delete my other file (prevent confusion) and attach this file which is your file with my NearMatch lambda added. I then used this equation:
=LET(nm,FILTER($A$11:$C$12,($B$11:$B$12=NearMatches(B3,$B$11:$B$12,,,," ")),""),XLOOKUP(C3,TAKE(nm,,-1),nm,"",-1))to find the near matches then to find the nearest $$ less than or equal to the value. I did NOT add any date condition as you haven't noted what that should be and you still need to determine how $$ and date should be prioritized. is a date 'close by' more important that an amount close to the value? how much so? how to balance the 2?
As another thought, if you want to go down the AI road, Excel has integrated an AI API call. See this article if you are interested: