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

[cdalgorta]

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.

RESULT sheet:

Test MrExcel.xlsx
	C			F	G						M	N	O	P
1	ID			Item	Balance						Match Failed (Accessorials)	Invoice Rejected (Accessorials)	Approval Failed (Accessorials)	Amount Due
2	33283896			Stop-off Fee-Sale	60						Accepted	Accepted	Accepted	60
3	33283896			Lumper Fee-Sale	410						#N/A	#N/A	#N/A	412
4	33283896			Lumper Fee-Sale	204						Accepted	Declined	Declined	204
Result

Cell Formulas
Range	Formula
M2:M4	M2	=INDEX(Data!E:E,MATCH(1,(Data!A:A=C2)*(Data!L:L=G2),0))
N2:N4	N2	=INDEX(Data!F:F,MATCH(1,(Data!A:A=C2)*(Data!L:L=G2),0))
O2:O4	O2	=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!

 

[KRice]

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

 

[cdalgorta]

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!!!!!!

 

[KRice]

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.