Multiple criteria Index Match with 1 criteria being within 10 digits and still match

cdalgorta

Board Regular
Joined
Jun 5, 2022
Messages
87
Office Version
  1. 365
Platform
  1. Windows
Hi,
I have 2 Sheets(workbooks in reality but I made the test sample in 2 sheets).

DATA sheet: All text/numbers. No formulas in this sheet.
1662630167277.png


RESULT sheet:
Test MrExcel.xlsx
CFGMNOP
1IDItemBalanceMatch Failed (Accessorials)Invoice Rejected (Accessorials)Approval Failed (Accessorials)Amount Due
233283896Stop-off Fee-Sale60AcceptedAcceptedAccepted60
333283896Lumper Fee-Sale410#N/A#N/A#N/A412
433283896Lumper Fee-Sale204AcceptedDeclinedDeclined204
Result
Cell Formulas
RangeFormula
M2:M4M2=INDEX(Data!E:E,MATCH(1,(Data!A:A=C2)*(Data!L:L=G2),0))
N2:N4N2=INDEX(Data!F:F,MATCH(1,(Data!A:A=C2)*(Data!L:L=G2),0))
O2:O4O2=INDEX(Data!G:G,MATCH(1,(Data!A:A=C2)*(Data!L:L=G2),0))


Note: Column P should be a formula, I added them manually because those are the numbers I need to get with the formula after I get help with the below.


The formulas I have on M2:O4 are simple Index Match with 2 criteria (cells on columns C and G on the Result sheet MATCH the columns A and L on the Data sheet).
As you can see on Data sheet cell L3, the number is 412, which does not match "exactly" with nothing on the Result sheet column G, so I get #N/A values for E3:G3.
Is there a way to do the Index Match with multiple criteria WHILE 1 of the criteria is not exact, but WITHIN 10 numbers of its closest matching value on the other table?
In other words, because my criteria is 410 on Result sheet cell G3, as long as the number on Data Sheet cell L3 is between 400 and 420(it is 412), then I should still be able to get results on cells M3:O3 instead of the #N/A.
Of course this is a multiple criteria, so it is only matching with the row with the 412 because it already matched the ID column first.

As for column P, I'd need it to show me the real value that it found on the Data Sheet column L. I'm guessing I'd just need to change the index array to that column(L:L), if I can get a formula for the above first.

Thank you in advance!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Try replacing (Data!L:L=G2) with (ABS(G2-Data!L:L)<=10)
...to allow for any values within 10. I'd also recommend using something other than full column references (e.g., Data!L$2:L$1000).
 
Upvote 0
Solution
You just saved my company weeks of work from an 850k rows of data. Took forever for the data to read all the formulas, but it finished and everyone shed a tear hahahah.
Thank you KRice! Anyone with a last name "Rice" will forever be on my "Great people I should befriend" list.
Thank you again!!!!!! 😊
 
Upvote 0
I'm glad that worked out...I'm happy to help. And thank you for the kind words. Incidentally, my comment about reworking the formula to not consider the entire column is motivated by making the sheet somewhat faster, since Excel wouldn't need to evaluate the entire column. In your case, if you have 850k rows, I doubt there is much to be gained since you're near the worksheet row limit anyway.
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

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