# Index + Match + Min +ABS + IF

#### cnoblesd01

##### New Member
So I started off thinking this was "easy"... I have two values and need to find the best match and return a result (3 columns)

Input 1: Distance (Actual)
Input 2: Capacity (Needed)

Column 1: Distance (Max) - Need to solve for closest larger than (Can do separately)
Column 2: Capacity (Available) - Need to solve for closest (Can do separately)
Column 3: Index (Type) - This is the the data to be returned

And if I use "helper cells" and try to solve for column 3, I get a "!REF" error....

Please see attached xlsx file for my attempt and sample / expected results.

HTML:
``https://drive.google.com/file/d/1k1N-7zNoEE0N6Q7Dw-sy9IfzFmdWtJAQ/view?usp=sharing``

THANK YOU EVERYONE IN ADVANCE FOR THE HELP!!

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### cnoblesd01

##### New Member
UPDATE: This is the formula I'm trying to use:

=INDEX(\$D\$4:\$D\$14,MATCH(MIN(IF(\$B\$4:\$B\$14=\$N\$8,ABS(N\$9>\$C\$4:\$C\$14))),IF(\$B\$4:\$B\$14=\$N\$8,ABS(N\$9>\$C\$4:\$C\$14)),0),3)

#### Peter_SSs

##### MrExcel MVP, Moderator
Welcome to the MrExcel board!

I do get one result different to you (blue cells). Perhaps I am not understanding the requirement fully or perhaps that expected result is incorrect?

Each formula copied across.

Excel Workbook
ABCDEFGHIJKLMNOPQR
2DATA
3DistCapType
458462103M
560502203ER
69710440B72Example 1Example 2
710567440B8-10SOLVE FORSOLVE FOR
811119550B7-3DIST14555DIST9711
9143154402ERCap1322661441330264Cap1619810540405324
1014315440B72ERRESULT (TYPE)B48iB48iB72LRB8-8B8-8RESULT (TYPE)B48iB48iB7-3B8-9B8-8
1114825730B48iMatch expected?TRUETRUETRUETRUEFALSETRUETRUETRUETRUETRUE
1215196381B8-8
1315556420B8-9
1417512440B72LRRESULT "SHOULD BE"B48iB48iB72LRB8-8B8-9RESULT "SHOULD BE"B48iB48iB7-3B8-9B8-8
Shee3

Last edited:

##### Well-known Member
I wondered that too, 264 is closer to 381 than 420.

#### cnoblesd01

##### New Member

Welcome to the MrExcel board!

Thank you! Looking through many posts here trying to discover why I am not grasping the basics of "INDEX"... there is clearly some genius at play here!

To point:

...or perhaps that expected result is incorrect?

Yes! My expected result was off.

I have literally tried solving this for the last 3 days... many attempts with all sorts of formulas. I cannot thank you enough!

Cheers kind Sir!

#### cnoblesd01

##### New Member
I wondered that too, 264 is closer to 381 than 420.

It proves that slight dyslexia and excel don't mix!! I bet Sir Branson never does his own. hehe.

Replies
2
Views
44
Replies
4
Views
152
Replies
5
Views
264
Replies
3
Views
267
Replies
0
Views
256

1,141,297
Messages
5,705,559
Members
421,399
Latest member
hjweiss00

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

### Which adblocker are you using?

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

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