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),"")

Copy down

b2:b12 are text

 

[Peter_SSs]

@NickFru
If you are asking how to do it with your version of Excel then you could use a formula like in post #8 or a shorter version like this
F2:=AGGREGATE(14,6,C$2:C$12/((B$2:B$12-MAX(B$2:B$12*(A$2:A$12=E2))=0)*(A$2:A$12=E2)),1)

However, since the OP has version 365, I'm not sure why we are canvassing more complex formulas than the pretty simple one suggested earlier.

 

[NickFru]

Yes, you are correct Peter, the original formula of VLOOKUP(H2:H4, SORT(A2:A14,2,-1), 3,0) worked perfectly in the 1st instance. Appreciate the other suggested formula you have stated above.