SOLVED

How to use XLOOKUP and IF function together

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3172284%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EHow%20to%20use%20XLOOKUP%20and%20IF%20function%20together%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3172284%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EHi%2C%20could%20someone%20kindly%20show%20me%20how%20to%20use%20XLOOKUP%20and%20IF%20function%20together.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3ECurrently%2C%20I%20have%203%20excel%20columns%3A%20Group%2C%20Sites%2C%20and%20Countries.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20find%20the%20median%20number%20of%20sites%20in%20United%20States%20but%20only%20if%20they're%20in%20Group%20%221%22.%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EThank%20you%2C%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EAmy%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3172284%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3172284%22%20slang%3D%22en-US%22%3EHow%20to%20use%20XLOOKUP%20and%20IF%20function%20together%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3172284%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20could%20someone%20kindly%20show%20me%20how%20to%20use%20XLOOKUP%20and%20IF%20function%20together.%3C%2FP%3E%3CP%3ECurrently%2C%20I%20have%203%20excel%20columns%3A%20Group%2C%20Sites%2C%20and%20Countries.%3C%2FP%3E%3CP%3EI%20would%20like%20to%20find%20the%20median%20number%20of%20sites%20in%20United%20States%20but%20only%20if%20they're%20in%20Group%20%221%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3EAmy%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3172284%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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 (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