getting next largest price from grid if no exact match found

[keithmct]

I need a reminder folks. I've been using index match for a long time on my price grids and have to re-arrange the grids (from suppliers) from left to right and top to bottom as highest value first and have a "-1" in there so that when exact price isn't found it grabs the next highest price when using my formula:
=IF(I15<>"",INDEX(INDIRECT(E15&AE15&"prices"),MATCH(I15,INDIRECT(E15&AE15&"height"),-1),MATCH(G15,INDIRECT(E15&AE15&"width"),-1)),"0")
But didn't I see somewhere that there had been a change so that you could put something in place of the "-1" and I wouldn't have to muck around with flipping price grids?
There shouldn't be a need to explain the cell references in the above, I just need to know about the possible general INDEX/MATCH improvement.

 

[arthurbr]

If your values are sorted in descending order, you can add the -1 switch to the MATCH function ( inside the function itself)

Match_type	Behavior
1 or omitted	MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
0	MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.
-1	MATCH finds the smallest value that is greater than or equal tolookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

 

[Fluff]

Nothing has changed in the Match function, however in 2021 & 365 there is the Xmatch function which does not need sorted data.

 

[keithmct]

Nothing has changed in the Match function, however in 2021 & 365 there is the Xmatch function which does not need sorted data.

Ah, that's it. However, I found this:
"What is the significance of the 1 in the function Xmatch?
Understanding the XMATCH Function in Excel
A match type of -1 means that the position within the array of the first entry less than or equal to the lookup value is returned and a match type of 1 means that the position within the array of the first entry more than the lookup value is returned."

I would like a 1 to mean more than or equal to the lookup value. I this just a misprint or will I always get the next highest value even when there is an exact match?

 

[Peter_SSs]

when exact price isn't found it grabs the next highest price

Ignoring all your INDIRECT issues, does this INDEX/MATCH/AGGREGATE combination do what you want?

23 11 16.xlsm
	A	B	C	D	E	F
1	Item	Price			Price	Item
2	A	2			5	F
3	D	8			2.1	Y
4	F	5
5	Y	3
Lookup

Cell Formulas
Range	Formula
F2:F3	F2	=INDEX(A$2:A$5,MATCH(AGGREGATE(15,6,B$2:B$5/(B$2:B$5>=E2),1),B$2:B$5,0))

 

[keithmct]

l also found this on exceljet which seems to answer (if you can trust it) whether "1" does what I want:

However, for approximate matches, the behavior is different when match_type is set to 1:

=MATCH(value,array,1) // exact match or next smallest
=XMATCH(value,array,1) // exact match or next *largest*

 

[Peter_SSs]

seems to answer (if you can trust it) whether "1" does what I want:

For MATCH at least, I think that you need to read further down that page ..

Also, if you are considering using XMATCH, does your forum profile need updating?
According to Microsoft Help on XMATCH it is not available in Excel 2019 as shown in your profile.

 

[keithmct]

For MATCH at least, I think that you need to read further down that page ..

View attachment 102013


Also, if you are considering using XMATCH, does your forum profile need updating?
According to Microsoft Help on XMATCH it is not available in Excel 2019 as shown in your profile.

Profile now updated. I'm going to go with XMATCH as it should find the exact value or the next highest value, as opposed to the statement from post #4 (I can't find where I sourced that now).

 

[Peter_SSs]

Profile now updated.

Cheers.

I'm going to go with XMATCH

Why not use XLOOKUP then? That will do the XMATCH and the INDEX all in one.

23 11 16.xlsm
	A	B	C	D	E	F
1	Item	Price			Price	Item
2	A	2			5	F
3	D	8			2.1	Y
4	F	5
5	Y	3
Lookup

Cell Formulas
Range	Formula
F2:F3	F2	=XLOOKUP(E2,B$2:B$5,A$2:A$5,,1)

 

[keithmct]

Cheers.

Why not use XLOOKUP then? That will do the XMATCH and the INDEX all in one.

Would there be any significant difference to stability or speed in the spreadsheet because if XMATCH works, that's good enough for me. Not sure if it matters but my 67 different shaped/sized price grids with named ranges for each height, width and price array make it less than exciting to jump in and change everything now. I don't even work for that company now. I'm just trying to make it easy for the owner.