Lookup delivering result of max number

[Peter Davison]

I have a lookup up "East of England" D2 in a cell from a separate workbook and I want to look into the data shown below.
Looking for the row that "East of England" is in and finding the largest number then delivering back to me the number that is in row 3
So the answer would be 131921 returning the number 1.
I just can't work out how I use either vlookup, xlookup or index and match.
Any help would be appreciated please.
Thank you.

 

[Peter_SSs]

Please consider XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if you could use something like this.

BTW, what should happen if the relevant row has 2 (or more) equal maximum values?

22 02 05.xlsm
	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1
2
3							1	2	3	4
4
5
6	f	2			a		841	270	843	507
7					b		921	721	958	801
8					c		369	154	811	985
9					d		751	211	803	253
10					e		578	689	365	372
11					f		82	326	141	42
12					g		872	680	752	837
13					h		872	791	730	657
14					i		835	299	647	492
15					j		173	115	834	667
16					k		548	3	6	271
17					l		226	61	449	166
18					m		526	121	389	838
19					n		73	977	850	16
Lookup

Cell Formulas
Range	Formula
X6	X6	=LET(rw,FILTER(AC6:AF19,AA6:AA19=W6),INDEX(AC3:AF3,MATCH(MAX(rw),rw,0)))

 

[Peter Davison]

Thank you most helpful. I've never used the "LET" before
I have tried to install XL2BB a couple of time but without great success but will try again
I did have a further mess around with the formula and came up with a bit of a long winded approach below, but seemed to work okay

XLOOKUP(MAX(INDEX('[Attribute Analysis.xlsm]Summary'!$AB:$AL,MATCH(F2,'[Attribute Analysis.xlsm]Summary'!$AA:$AA,0),0)),INDEX('[Attribute Analysis.xlsm]Summary'!$AB:$AL,MATCH(F2,'[Attribute Analysis.xlsm]Summary'!$AA:$AA,0),0),'[Attribute Analysis.xlsm]Summary'!$AB$3:$AL$3,"")

 

[Peter_SSs]

I have tried to install XL2BB a couple of time but without great success but will try again

If you would like to tell us about ..
- at what point in these instructions do you run aground, and
- what the actual problem is at that point
.. then we can quite possible assist with resolving the problem

I did have a further mess around with the formula and came up with a bit of a long winded approach below, but seemed to work okay

If you wanted to persist with XLOOKUP, then LET could also make that much shorter.
I have assumed that cell F2 in the formula sheet and column AA in the Summary sheet are text values not numerical.

=LET(rng,'[Attribute Analysis.xlsm]Summary'!$AA:$AL,rw,INDEX(rng,MATCH(F2,INDEX(rng,0,1),0),0),XLOOKUP(MAX(rw),rw,INDEX(rng,3,0)))

Also, you did not address the question below. Is that situation possible with your data?

BTW, what should happen if the relevant row has 2 (or more) equal maximum values?

 

[Peter Davison]

I'll have another go at XL2BB.
Regarding the duplicate numbers - That won't be a concern for this calculation because this data is for store clusters and if the number is the same it means the cluster could be either one or the other, so not an issue.

 

[Peter_SSs]

OK, thanks for the clarification.