Forum Discussion

geraldvassallo's avatar
geraldvassallo
Copper Contributor
Jul 14, 2020
Solved

Formula Help - XLookup + average

Hello,    I am trying to work out a formula where, XLOOKUP finds the data in 3 different worksheets, and gives me the average, however, whenever it is not finding a value in 1 sheet, it is coming u...
excel
office 365
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 14, 2020

geraldvassallo 

In general the same

=MIN(1,Target!$C$3*AVERAGE(
       IFNA(1/XLOOKUP($A3,'Data 1'!$K:$K,'Data 1'!A:A,NA()),FALSE),
       IFNA(1/XLOOKUP($A3,'Data 2'!$K:$K,'Data 2'!A:A,NA()),FALSE),
       IFNA(1/XLOOKUP($A3,'Data 3'!$K:$K,'Data 3'!A:A,NA()),FALSE)
))

 

Resources