CODEWITHSUNDEEP
excelexcel-formula
Rahul
Rahul

Reputation: 13

Get data of Maximum value in Excel

I have 2 tables like attached image

enter image description here

I want formula for Table 2 PF column.

Formula should check where the person performed maximum duty (in table 2) and get PF from Table 1. For example, Raj performed maximum duty in HUL, then his PF should be inserted in the same row, all other rows containing the name Raj should remain blank.

Till now, i tried Xlookup to get the value from another table.

=XLOOKUP([@Name],Table1[Name],Table1[PF],"")

Above formula fill every row for the same name.

I dont know how to check which name has maximum duty and then drive xlookup to get that value from another table.

I am using office 365 monthly targeted version so all the Dynamic array formulas available with me.

Thanks

Upvotes: 0

Views: 81

Answers (1)

Scott Craner
Scott Craner

Reputation: 152605

Use:

=IF(MAXIFS([Duty],[Name],[@Name])=[@Duty],XLOOKUP([@Name],Table1[Name],Table1[PF]),"")

Edited the formula (removed extra = sign before xlookup

Upvotes: 1

Related Questions