Index Match with Max Function

[nathanthomson11]

I am looking for a way to pull pricing based on multiple criteria, one being max.

For example, column A is contracts, and I want to pull pricing (column C) based on the latest (highest) revision in column B.

Expected results -

Contact	Latest Price
5000002990	1.76
5000054767	0.78

 

[jkpieterse]

XLOOKUP will give you an argument to specify whether to look from top to bottom, or vice versa.

 

[nathanthomson11]

I should note that the "Revision" column won't always be in the right order.

 

[Peter_SSs]

pull pricing (column C) based on the latest (highest) revision in column B

Like this?
I have assumed all the revision codes are text values & will be the same length - 5 digits

25 03 01.xlsm
	A	B	C	D	E	F
1	Contract	Revision	Price		Contract	Latest
2	1	00010	6		1	3
3	2	00030	18		2	17
4	3	00123	1		3	1
5	2	00050	9
6	3	00060	5
7	1	00040	13
8	2	00001	11
9	3	00080	9
10	1	00090	3
11	2	00100	17
12	3	00095	6
Lookup Highest

Cell Formulas
Range	Formula
F2:F4	F2	=VLOOKUP(E2:E4,SORT(A2:C12,2,-1),3,0)
Dynamic array formulas.

 

[MARZIOTULLIO]

F2=MAXIFS(C2:C12,B2:B12,MAXIFS(B2:B12,A2:A12,E2:E4))

 

[Peter_SSs]

@MARZIOTULLIO
If you look at the image uploaded in post 1, you will see that column B has green triangles in the corner of each cell, presumably indicating that those values are "numbers stored as text" which would defeat your formula as written.

 

[NickFru]

@MARZIOTULLIO
If you look at the image uploaded in post 1, you will see that column B has green triangles in the corner of each cell, presumably indicating that those values are "numbers stored as text" which would defeat your formula as written.

Hi Peter - just had a look at this now. If we used within the formula MAX((B2:B24)*1), this would convert the numbers to a number rather than text, in this case being 120. I have however then tried to use an INDEX on the price range with this MAX to try and calculate the price being £1.76 on 5000002990 but not having much joy?

 

[MARZIOTULLIO]

F2=IFERROR(AGGREGATE(14,6,$C$2:$C$12/(AGGREGATE(14,6,VALUE($B$2:$B$12)/($A$2:$A$12=E2),1)=VALUE($B$2:$B$12)),1),"")