SOLVED

How to use XLOOKUP and IF function together

Brass Contributor

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 (Brass Contributor)
Solution

@AmyYang 

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

Hi Quadrupule Pawn,

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

Regards,
Amy

@AmyYang 

As variant

=MEDIAN(
    FILTER(
        Table1[Citeline Reported Sites],
        (Table1[Group] = 1) *
            ISNUMBER(
                SEARCH(
                    "United States",
                    Table1[Citeline Countries]
                )
            )
    )
)
Well noted, thanks so much again Sergei for your helpful reply!

@AmyYang , glad to help

1 best response

Accepted Solutions
best response confirmed by AmyYang (Brass Contributor)
Solution

@AmyYang 

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

View solution in original post