Feb 16 2022 01:39 AM
Hi, could someone kindly show me how to use XLOOKUP and IF function together.
Currently, I have 3 excel columns: Group, Sites, and Countries.
I would like to find the median number of sites in United States but only if they're in Group "1".
Thank you,
Amy
Feb 16 2022 02:20 AM
Solution=MEDIAN(IF(ISNUMBER(SEARCH("United States",Table1[Citeline Countries]))*(Table1[Group]=1),Table1[Citeline Reported Sites]))
Is this what you are looking for? Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021.
Feb 16 2022 06:29 AM
Feb 16 2022 07:14 AM
As variant
=MEDIAN(
FILTER(
Table1[Citeline Reported Sites],
(Table1[Group] = 1) *
ISNUMBER(
SEARCH(
"United States",
Table1[Citeline Countries]
)
)
)
)
Feb 17 2022 12:45 AM
Feb 16 2022 02:20 AM
Solution=MEDIAN(IF(ISNUMBER(SEARCH("United States",Table1[Citeline Countries]))*(Table1[Group]=1),Table1[Citeline Reported Sites]))
Is this what you are looking for? Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021.