SOLVED

Contributor

# 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[Source]=Citeline)Table7[RR]) but unfortunately it did not work and I don't understand where I made mistake.

It would greatly help if there's a formula so I don't need to look up each value for each country individually, thank you.

Thanks so much,

Amy

4 Replies

# Re: How to use Xlookup with median and IF functions together?

best response confirmed by AmyYang (Contributor)
Solution

# Re: How to use Xlookup with median and IF functions together?

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

# Re: How to use Xlookup with median and IF functions together?

Dear Detlef Lewin, thanks kindly for your time to reply so promptly, apologies, I feel bad because I feel when there's tiny tweak to new situation, my limited excel knowledge makes me quite confused and then I don't how to use the excel functions, thanks so much for your reply and help.

# Re: How to use Xlookup with median and IF functions together?

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.