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