Xlook to find max value

[hananak]

Dear All,

I am trying to do a look up value formula and my aim is to lookup the names and pick the highest daily rate.

I have a table where I haev list of names of people who worked on different days, same person's name is on this table multiple times but having different daily rates. e.g: Alex has worked on different days and had different daily rates. I have another table where I want to do look up to find Alex and give me the highest daily rate as outcome.

I have tried to use max function within xlookup but not working.

Agency Tracker.xlsx
	H	I	J
10	Table 1
11	Name	Daily Rate
12	Alex	350
13	David	400
14	Nur	500
15	Ruth	600
16	Alex	500
17	David	800
18	Nur	900
19	Ruth	1000
20
21	Result Table - Vlookup		Result I want
22	Name	Daily Rate
23	Alex	350	The Highest Value from above Table
24	David	400	The Highest Value from above Table
25	Nur	500	The Highest Value from above Table
26	Ruth	600	The Highest Value from above Table
Accrual Calculation

Cell Formulas
Range	Formula
I23:I26	I23	=VLOOKUP(H23,$H$12:$I$19,2,0)

Your help would be really appreciated.

 

[fgfdg456]

hello,

Try: ={MAX(IF($H$12:$H$19=H23,$I$12:$I$19))}

Xlookup only returns one value

 

[kevin9999]

Or with 365 (as per your profile)

Book1
	H	I	J
10	Table 1
11	Name	Daily Rate
12	Alex	350
13	David	400
14	Nur	500
15	Ruth	600
16	Alex	500
17	David	800
18	Nur	900
19	Ruth	1000
20
21	Result Table - Vlookup		Result I want
22	Name	Daily Rate
23	Alex	500	The Highest Value from above Table
24	David	800	The Highest Value from above Table
25	Nur	900	The Highest Value from above Table
26	Ruth	1000	The Highest Value from above Table
Sheet3

Cell Formulas
Range	Formula
I23:I26	I23	=MAXIFS($I$12:$I$19,$H$12:$H$19,H23)

 

[yky]

I modified you question by specifying the range of names and values and the address of Alex and fed it to chatgpt. The following is the modified question and the result chatgpt gave. It first gave an answer same as kevin9999's. I told it I'm using Excel 2010. It then gave fgfdg456's answer.

question: "I am trying to do a look up value formula and my aim is to lookup the names and pick the highest daily rate. I have a table where I haev list of names of people who worked on different days, same person's name is on this table multiple times but having different daily rates. e.g: Alex has worked on different days and had different daily rates. I have another table where I want to do look up to find Alex and give me the highest daily rate as outcome. The range of the names is in H12:H19 and values are in I12:I19. please give me the formula for getting the highest daily rate. Alex is in H23, I have tried to use max function within xlookup but not working."

answer: "
You can use a combination of the MAX and IF functions to accomplish this. Here's an example formula that should work for your scenario:
=MAX(IF(H12:H19=H23,I12:I19))
This formula will check if each cell in the range H12:H19 matches the name "Alex" in H23. If there is a match, it will include the corresponding value in I12:I19 in the calculation. Finally, it will return the highest value from the resulting array using the MAX function.
Make sure to enter this formula as an array formula by pressing Ctrl + Shift + Enter after typing it in.
"

 

[alansidman]

A power query solution

Book1
	A	B	C	D	E
1	Table 1
2	Name	Daily Rate		Name	Max Per Person
3	Alex	350		Alex	500
4	David	400		David	800
5	Nur	500		Nur	900
6	Ruth	600		Ruth	1000
7	Alex	500
8	David	800
9	Nur	900
10	Ruth	1000
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Daily Rate", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Max Per Person", each List.Max([Daily Rate]), type nullable number}})
in
    #"Grouped Rows"

 

[Peter_SSs]

If each person's rate always gets higher as you move down Table 1 like your sample data (that is, you can get the highest rate by finding the last instance of the person's name) then you could get all the results with a single formula in cell I23 like this.

23 02 26.xlsm
	H	I
10	Table 1
11	Name	Daily Rate
12	Alex	350
13	David	400
14	Nur	500
15	Ruth	600
16	Alex	500
17	David	800
18	Nur	900
19	Ruth	1000
20
21	Result Table - Vlookup
22	Name	Daily Rate
23	Alex	500
24	David	800
25	Nur	900
26	Ruth	1000
Highest rate

Cell Formulas
Range	Formula
I23:I26	I23	=XLOOKUP(H23:H26,H12:H19,I12:I19,,,-1)
Dynamic array formulas.

 

[JIMBO992]

Use the 'MAXIFS' formula? Unless i'm missing something, this is an easy fix?

James.

 

[shinigamilight]

Ctrl shift enter for older versions of excel.
B14:

=MAX(IF(A14=$A$3:$A$10,$B$3:$B$10))

 

[Peter_SSs]

Use the 'MAXIFS' formula? Unless i'm missing something, this is an easy fix?

James.

Wasn't that already suggested in post #3?

Ctrl shift enter for older versions of excel.
B14:

=MAX(IF(A14=$A$3:$A$10,$B$3:$B$10))

Wasn't that already suggested in post #2 (only with the correct ranges)?

 

[jdellasala]

If you sort the data, VLOOKUP does work:

There's also a DMAX solution, but it's not pretty:

Book1
	H	I
10	Table 1
11	Name	Daily Rate
12	Alex	500
13	Alex	350
14	David	800
15	David	400
16	Nur	900
17	Nur	500
18	Ruth	1000
19	Ruth	600
20
21	Result Table - DMAX
22	Name	Daily Rate
23	Alex	500
24	David	800
25	Nur	900
26	Ruth	1000
27
28	Name	Daily Rate
29	=Alex	>350
30
31	Name	Daily Rate
32	=David	>350
33
34	Name	Daily Rate
35	=Nur	>350
36
37	Name	Daily Rate
38	Ruth	>350
Sheet1

Cell Formulas
Range	Formula
I23	I23	=DMAX($H$11:$I$19,"Daily Rate",H28:I29)
I24	I24	=DMAX($H$11:$I$19,"Daily Rate",H31:I32)
I25	I25	=DMAX($H$11:$I$19,"Daily Rate",H34:I35)
I26	I26	=DMAX($H$11:$I$19,"Daily Rate",H37:I38)
H29	H29	="=Alex"
I29,I38,I35,I32	I29	=">"&MIN($I$12:$I$19)
H32	H32	="=David"
H35	H35	="=Nur"
H38	H38	="Ruth"

BTW, using Merge & Center really screws up the structure of a Worksheet. Not so much in this example, but generally a really bad idea. Better to use Center Across Selection in the Alignment options:

Those are 2016 solutions. For 365, MAXIFS is certainly the way to go, although if you're dealing with a lot more data the Power Query method is best and works in both versions.