Wildcard search within IF and LARGE / SMALL INDEX searches.

**Mainiac** · 06-03-2013, 10:34 AM

Hello all,

First post here as I have finally run out of places to search.

I am using a formula to lookup the largest (or kth largest/smallest) value on another sheet {entered as an array}:

=INDEX(SHEET1!$H$2:$H$827, LARGE(IF($C1=SHEET1!$G$2:$G$827, ROW(SHEET1!$G$2:$G$827)-MIN(ROW(SHEET1!$G$2:$G$827))+1, ""), COLUMN($A$1)))

I wanted to add a wildcard to the value being searched such as $C1&"*" as I've done with INDEX MATCH functions before but that didn't work (I assume because the IF function)and after searching forums arrived at this below, which still hasn't worked:

=INDEX(SHEET1!$H$2:$H$827, LARGE(IF(ISNUMBER(SEARCH(SHEET1!$G$2:$G$827,$C1)), ROW(SHEET1!$G$2:$G$827)-MIN(ROW(SHEET1!$G$2:$G$827))+1, ""), COLUMN($A$1)))

Any suggestion on adding a wild card to the cell being searched (C1) since the cells in Sheet1 col H have additional characters on the end? Help much appreciated.

**daddylonglegs** · 06-03-2013, 10:38 AM

SEARCH works for the text anywhere within the cell - your suggestion has the arguments the wrong way round within SEARCH function.

.....but for text beginning with C1 use LEFT function like this:

=INDEX(SHEET1!$H$2:$H$827, LARGE(IF($C1=LEFT(SHEET1!$G$2:$G$827,LEN($C1)), ROW(SHEET1!$G$2:$G$827)-MIN(ROW(SHEET1!$G$2:$G$827))+1, ""), COLUMN($A$1)))