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 {these are entered as arrays}:
=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.
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 {these are entered as arrays}:
=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.
