How to use Xlookup with median and IF functions together?

Hello Excel Community, I am wondering if someone could advise on how to use Xlookup with Median + IF function together? I tried this formula: =XLOOKUP(A7,Table7[Country],MEDIAN(IF(Table7[Sou...

Xlookup with Median and IF Function Question_19March.xlsx13 KB

excel

Riny_van_Eekelen
Mar 19, 2022
AmyYang I didn't bother to look at any of the previous questions and answers, but this one seems to do what you asked for.
In B7:

=MEDIAN(FILTER(Table1[RR],(Table1[Source]="Citeline")*(Table1[Country]=[@Country])))

And a similar one in C7. just change "Citeline" to "Q2Labs".

Edit: Note that the Table you refer to as "Table2" is actually called "Table1". And the formula you have problems with refers to a Table7 that doesn't exist. Not in this sheet in any case.

Riny_van_Eekelen

Platinum Contributor

Mar 19, 2022

AmyYang I didn't bother to look at any of the previous questions and answers, but this one seems to do what you asked for.

In B7:

=MEDIAN(FILTER(Table1[RR],(Table1[Source]="Citeline")*(Table1[Country]=[@Country])))

And a similar one in C7. just change "Citeline" to "Q2Labs".

Edit: Note that the Table you refer to as "Table2" is actually called "Table1". And the formula you have problems with refers to a Table7 that doesn't exist. Not in this sheet in any case.

AmyYang

Brass Contributor

Mar 19, 2022

Dear Riny, thanks so much for the prompt reply, I have used this formula for work and it just saved me a ton of time, really appreciate your support.

Forum Discussion

How to use Xlookup with median and IF functions together?

Resources