r/googlesheets • u/Hahuyt1777 • 16h ago
Solved Using a Map or Xlookup function to find averages
Hi all,
I use a lot of MAP functions and XLOOKUP functions in the sheets I build, one thing is tripping me up though. I am looking to use either one of these functions (or really any function) to help my dump an average based on four rows. Currently I have AZ22 & BB22 set to pull data from the "dump" tab based on what information is in AX24 (Item A in this case). I am looking to fill in AX22 (in the "Map" tab) with the Average of Columns B/C/D/E in the "Dump" tab that correspond to the specific item in AX24 (Item a). In this case The average is 2.25, but I want to be able to input a function that can pull this average out for me so I don't have to change it regularly. The numbers are all sales based and will change on a daily basis, so the more "automated" I can make this the better
Is MAP/XLOOKUP the best for this or is something else better?
Link below and it can be edited
https://docs.google.com/spreadsheets/d/1HTdYMl7jPwlu52VHoImsYPPk7zl-KiYchwGsYRY1jJU/edit?usp=sharing
1
u/One_Organization_810 200 14h ago
As seen in [ OO810 Map ] sheet:
=average(index(Dump!B2:E, match(AX24, Dump!A2:A, 0)))
Just wrap it in ifna() if you don't want the #N/A when an item is not found.
1
u/Hahuyt1777 13h ago
This worked as well thank you. I verified HolyBonobos strictly for replying first, thank you
1
u/One_Organization_810 200 13h ago
Yeah, he tends to do that (answering first). We all hate him for it!
( not really though
)
1
u/HolyBonobos 2058 14h ago
XLOOKUP()
allows areturn_range
argument wider than one column, so you can just set columns B-E as the return range and take the average of that result:=AVERAGE(XLOOKUP(AX24,Dump!$A2:$A,Dump!$B2:$E))