Pivot table

**jessetrunk** · 11-08-2009, 10:02 AM

I've got two columns with data. The first is text, and the second is numbers. So I want to be able to enter a certain criteria that would be contained in the text, and have excel return a list of the rows of text that contain that criteria and that have the highest 3 values in the corresponding column. I can use multiple cells to do this if need be (i.e. one cell for the text with the highest value, one for the second highest value, and a third for the next highest value).

See attachment for further explanation.

**martindwilson** · 11-08-2009, 03:20 PM

try

=LARGE(--(ISNUMBER(SEARCH("18a",A4:A25))*(B4:B25)),1) highest

=LARGE(--(ISNUMBER(SEARCH("18a",A4:A25))*(B4:B25)),2) 2nd
=LARGE(--(ISNUMBER(SEARCH("18a",A4:A25))*(B4:B25)),3) 3rd
all these are array formulas see my sig. below for how to enter arrays

**jessetrunk** · 11-08-2009, 06:15 PM

Thank you, that actually solved my next step in the worksheet. For the step I was inquiring about, though, I actually need to return a list of the description text itself for the 3 highest causes. I tried to use the formula you wrote and added the "offset" function to it with a -1 in the column criteria, hoping it would return the actual text in the cell, but I got an error message. Any suggestions???

**martindwilson** · 11-08-2009, 06:32 PM

this works(but it suprised me it did!)
=INDEX($A$4:$A$25, MATCH("*18a*" & D4, INDEX($A$4:$A$25 & $B$4:$B$25, 0), 0)) where d4 is the result of
{=LARGE(--(ISNUMBER(SEARCH("18a",A4:A25))*(B4:B25)),1)}
and so on