SOLVED

How to use Xlookup with median and IF functions together?

%3CLINGO-SUB%20id%3D%22lingo-sub-3261606%22%20slang%3D%22en-US%22%3EHow%20to%20use%20Xlookup%20with%20median%20and%20IF%20functions%20together%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3261606%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Excel%20Community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20wondering%20if%20someone%20could%20advise%20on%20how%20to%20use%20Xlookup%20with%20Median%20%2B%20IF%20function%20together%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20this%20formula%3A%26nbsp%3B%3DXLOOKUP(A7%2CTable7%5BCountry%5D%2CMEDIAN(IF(Table7%5BSource%5D%3DCiteline)Table7%5BRR%5D)%20but%20unfortunately%20it%20did%20not%20work%20and%20I%20don't%20understand%20where%20I%20made%20mistake.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20greatly%20help%20if%20there's%20a%20formula%20so%20I%20don't%20need%20to%20look%20up%20each%20value%20for%20each%20country%20individually%2C%20thank%20you.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much%2C%3C%2FP%3E%3CP%3EAmy%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3261606%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3261723%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20Xlookup%20with%20median%20and%20IF%20functions%20together%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3261723%22%20slang%3D%22en-US%22%3EDear%20Riny%2C%20thanks%20so%20much%20for%20the%20prompt%20reply%2C%20I%20have%20used%20this%20formula%20for%20work%20and%20it%20just%20saved%20me%20a%20ton%20of%20time%2C%20really%20appreciate%20your%20support.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3261722%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20Xlookup%20with%20median%20and%20IF%20functions%20together%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3261722%22%20slang%3D%22en-US%22%3EDear%20Detlef%20Lewin%2C%20thanks%20kindly%20for%20your%20time%20to%20reply%20so%20promptly%2C%20apologies%2C%20I%20feel%20bad%20because%20I%20feel%20when%20there's%20tiny%20tweak%20to%20new%20situation%2C%20my%20limited%20excel%20knowledge%20makes%20me%20quite%20confused%20and%20then%20I%20don't%20how%20to%20use%20the%20excel%20functions%2C%20thanks%20so%20much%20for%20your%20reply%20and%20help.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3261616%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20Xlookup%20with%20median%20and%20IF%20functions%20together%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3261616%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1260738%22%20target%3D%22_blank%22%3E%40AmyYang%3C%2FA%3E%26nbsp%3BI%20didn't%20bother%20to%20look%20at%20any%20of%20the%20previous%20questions%20and%20answers%2C%20but%20this%20one%20seems%20to%20do%20what%20you%20asked%20for.%3C%2FP%3E%3CP%3EIn%20B7%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DMEDIAN(FILTER(Table1%5BRR%5D%2C(Table1%5BSource%5D%3D%22Citeline%22)*(Table1%5BCountry%5D%3D%5B%40Country%5D)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BAnd%20a%20similar%20one%20in%20C7.%20just%20change%20%22Citeline%22%20to%20%22Q2Labs%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEdit%3A%20Note%20that%20the%20Table%20you%20refer%20to%20as%20%22Table2%22%20is%20actually%20called%20%22Table1%22.%20And%20the%20formula%20you%20have%20problems%20with%20refers%20to%20a%20Table7%20that%20doesn't%20exist.%20Not%20in%20this%20sheet%20in%20any%20case.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3261612%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20Xlookup%20with%20median%20and%20IF%20functions%20together%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3261612%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1260738%22%20target%3D%22_blank%22%3E%40AmyYang%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20have%20already%20asked%20a%20similar%20question%20and%20got%20some%20answers%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fcreating-excel-function-formula-to-automatically-calculate%2Fm-p%2F3248519%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fcreating-excel-function-formula-to-automatically-calculate%2Fm-p%2F3248519%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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
best response confirmed by AmyYang (Contributor)
Solution

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