SOLVED

Array formula SUMIF no responding

%3CLINGO-SUB%20id%3D%22lingo-sub-1403190%22%20slang%3D%22en-US%22%3EArray%20formula%20SUMIF%20no%20responding%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1403190%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3EI%20insert%20an%20array%20formula%20in%20cell%20x40%20down%20to%20x47%20(sets%20as%20general%20format)%20looking%20for%20a%20specific%20word%20from%20a%20table%20m38%20to%20t57%2C%20in%20order%20from%20number12%20down%20to%206.%20Columns%20M%2C%20O%20Q%2C%20and%20S%20are%20numerical%2C%20and%20the%20text%20or%20words%20searched%20are%20in%20columns%20N%2C%20P%2C%20R%2C%20and%20T.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20taking%20data%20from%20a%20dynamic%20table%2C%20but%20in%20the%20other%20table%20just%20I%20need%20results%20in%20a%20column%20(x)%20bringing%20the%20highest%20first%20seven%20words%20in%20ascending%20order.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1403190%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Hello!

I insert an array formula in cell x40 down to x47 (sets as general format) looking for a specific word from a table m38 to t57, in order from number12 down to 6. Columns M, O Q, and S are numerical, and the text or words searched are in columns N, P, R, and T.

 

I'm taking data from a dynamic table, but in the other table just I need results in a column (x) bringing the highest first seven words in ascending order.

2 Replies
Best Response confirmed by Analytic_JCPS (New Contributor)
Solution

Hi @Analytic_JCPS,

 

You may trying using the below formula & it should work fine 

 

=INDIRECT(ADDRESS(
MAX(IF(LARGE($M$38:$T$57,$W41)=$M$38:$T$57,ROW($M$38:$M$57))),MAX(IF(LARGE($M$38:$T$57,$W41)=$M$38:$T$57,1+COLUMN($M$38:$T$38))),1,1),TRUE)

 

2020-05-22_07-25-27.png

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more

 

Hi Shaikh!
Thank you.
I appreciate a lot your help. The formula just fit fine and ease!!