Reputation: 13
I have 2 tables like attached image
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
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