Index Match Not returning expected answer

[ddocg]

Hi,

I am using the following formula:

=INDEX(D4:D35,MATCH(MIN(ABS(D4:D35-B13)),ABS(D4:D35-B13),-1))

Cell B13 contains the number 660

My array contains the following list sorted into descending order:

[TABLE="width: 64"]
[TR]
[TD="class: xl63, width: 64"]4680[/TD]
[/TR]
[TR]
[TD="class: xl63"]4100[/TD]
[/TR]
[TR]
[TD="class: xl63"]3330[/TD]
[/TR]
[TR]
[TD="class: xl63"]2900[/TD]
[/TR]
[TR]
[TD="class: xl63"]2530[/TD]
[/TR]
[TR]
[TD="class: xl63"]2430[/TD]
[/TR]
[TR]
[TD="class: xl63"]2210[/TD]
[/TR]
[TR]
[TD="class: xl63"]2020[/TD]
[/TR]
[TR]
[TD="class: xl63"]1980[/TD]
[/TR]
[TR]
[TD="class: xl63"]1900[/TD]
[/TR]
[TR]
[TD="class: xl63"]1800[/TD]
[/TR]
[TR]
[TD="class: xl63"]1640[/TD]
[/TR]
[TR]
[TD="class: xl63"]1490[/TD]
[/TR]
[TR]
[TD="class: xl63"]1470[/TD]
[/TR]
[TR]
[TD="class: xl63"]1370[/TD]
[/TR]
[TR]
[TD="class: xl63"]1330[/TD]
[/TR]
[TR]
[TD="class: xl63"]1280[/TD]
[/TR]
[TR]
[TD="class: xl63"]1220[/TD]
[/TR]
[TR]
[TD="class: xl63"]1210[/TD]
[/TR]
[TR]
[TD="class: xl63"]1100[/TD]
[/TR]
[TR]
[TD="class: xl63"]1060[/TD]
[/TR]
[TR]
[TD="class: xl63"]974[/TD]
[/TR]
[TR]
[TD="class: xl63"]869[/TD]
[/TR]
[TR]
[TD="class: xl63"]847[/TD]
[/TR]
[TR]
[TD="class: xl63"]792[/TD]
[/TR]
[TR]
[TD="class: xl63"]750[/TD]
[/TR]
[TR]
[TD="class: xl63"]692[/TD]
[/TR]
[TR]
[TD="class: xl63"]649[/TD]
[/TR]
[TR]
[TD="class: xl63"]591[/TD]
[/TR]
[TR]
[TD="class: xl63"]566[/TD]
[/TR]
[TR]
[TD="class: xl63"]534[/TD]
[/TR]
[TR]
[TD="class: xl63"]504


[/TD]
[/TR]
[/TABLE]
My expectation was for the formula to return 692 however it doesn't, using either type 0 or -1 always returns 649 and type 1 returns N\A regardless of how the array is sorted.

I'd very much appreciate if someone could explain what I need to do, to get the formula to return 692 i.e. Match Type -1 [FONT=&quot]MATCH finds the smallest value greater than or equal to lookup value. Lookup array must be sorted in descending order.

Kind regards
Dave.[/FONT]

 

[jim may]

Your Cells in the first column below are in Cells C30,C31, & C32


692 32 27
649 -11 28
591 -69 29


Where the ABS(-11) returns the Minimum 11, which the match returns the 28th number or 649.

 

[ddocg]

Thanks for your reply Jim,

I maybe wasn't clear in my original question.

Perhaps I'm using the wrong formula? I have obviously misunderstood the use of -1 at the end of my formula. My understanding was that using -1 would result in:

finds the smallest value greater than or equal to lookup value. Lookup array must be sorted in descending order.

As my lookup value is 660 and with the above in mind, I would expect 692 to be returned but as you point out it returns 649.

Any advice on how I get it to output 692 which would be the smallest value greater than the lookup value?

Kind regards
Dave

 

[AhoyNC]

Try this array formula. Enter with CTRL-SHIFT-ENTER.

Excel Workbook
	B	C	D
4	692		4680
5			4100
6			3330
7			2900
8			2530
9			2430
10			2210
11			2020
12			1980
13	660		1900
14			1800
15			1640
16			1490
17			1470
18			1370
19			1330
20			1280
21			1220
22			1210
23			1100
24			1060
25			974
26			869
27			847
28			792
29			750
30			692
31			649
32			591
33			566
34			534
35			504
Sheet

 

[AhoyNC]

Actual I think one of these shorter formulas will do what you want:

If you have a later ver. of Excel with the MINIFS function then the formula in B4. If not then the formula in B6 that must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D
4		692		4680
5				4100
6		692		3330
7				2900
8				2530
9				2430
10				2210
11				2020
12				1980
13		660		1900
14				1800
15				1640
16				1490
33				566
34				534
35				504
Sheet

 

[ddocg]

Thanks AhoyNC your first example formula is exactly what I was looking for. I really appreciate you taking the time in providing it.

Edit, I missed your follow up post providing further examples, this is just awesome, I'll have a look at those, again, many thanks.

Kind regards
Dave

 

[AhoyNC]

You're welcome. Thanks for the feedback.