Forum Discussion
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
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.
4 Replies
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.
- 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.
You have already asked a similar question and got some answers:
- 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.
