SOLVED

Contributor

How to use XLOOKUP and IF function together

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

5 Replies
best response confirmed by AmyYang (Contributor)
Solution

Re: How to use XLOOKUP and IF function together

``=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.

Re: How to use XLOOKUP and IF function together

Thank you so much!! That is amazing, and exactly what I was looking for! Thank you so much for sharing the formula.

Regards,
Amy

Re: How to use XLOOKUP and IF function together

As variant

``````=MEDIAN(
FILTER(
Table1[Citeline Reported Sites],
(Table1[Group] = 1) *
ISNUMBER(
SEARCH(
"United States",
Table1[Citeline Countries]
)
)
)
)``````