Index + Match + Min +ABS + IF

cnoblesd01

New Member
Joined
Jul 7, 2018
Messages
4
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
Joined
Jul 7, 2018
Messages
4
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
Joined
May 28, 2005
Messages
50,601
Office Version
  1. 365
Platform
  1. Windows
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:

cnoblesd01

New Member
Joined
Jul 7, 2018
Messages
4

ADVERTISEMENT

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!
 

Forum statistics

Threads
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.
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
Top