Index + Match + Min +ABS + IF

[cnoblesd01]

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.

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

THANK YOU EVERYONE IN ADVANCE FOR THE HELP!!

 

[cnoblesd01]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2		DATA
3		Dist	Cap	Type
4		5846	210	3M
5		6050	220	3ER
6		9710	440	B72		Example 1							Example 2
7		10567	440	B8-10		SOLVE FOR							SOLVE FOR
8		11119	550	B7-3		DIST	14555						DIST	9711
9		14315	440	2ER		Cap	1322	661	441	330	264		Cap	1619	810	540	405	324
10		14315	440	B72ER		RESULT (TYPE)	B48i	B48i	B72LR	B8-8	B8-8		RESULT (TYPE)	B48i	B48i	B7-3	B8-9	B8-8
11		14825	730	B48i		Match expected?	TRUE	TRUE	TRUE	TRUE	FALSE			TRUE	TRUE	TRUE	TRUE	TRUE
12		15196	381	B8-8
13		15556	420	B8-9
14		17512	440	B72LR		RESULT "SHOULD BE"	B48i	B48i	B72LR	B8-8	B8-9		RESULT "SHOULD BE"	B48i	B48i	B7-3	B8-9	B8-8
Shee3

 

[sheetspread]

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

 

[cnoblesd01]

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]

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.

 

[Peter_SSs]

I cannot thank you enough!

You just did.