Spreadsheet: 215719d1361462991-get-maximum-value-per-row-of-an-embedded-2d-array-to-create-an-embedded-1d-column-vector-excel-problem-locate-string-in-array
Sheet: Locate String in Array 4

Locate a given input string in an array and list the item associated with the row that string occupies (Solution 4)
 |  |  |  |  |  |  |  |  |  | All improvements achieved, but extra 'sorted' column introduced
[NAME]: |  | Match all Items where a given name occurs, and all the times it occurs
 |  |  |  |  |  |  |  |  |  | Unsorted | Sorted
 |  | Item | Name 1 | Name 2 | Name 3 | Name 4 | Name 5 | Name= | *avi* | Item Row | Item Row | Item | [NAME]) | Found(2) | [NAME]) | [NAME]) | Found(5)
 |  | First | [NAME] | [NAME] | [NAME] | [NAME] | [NAME]
 |  | Second | [NAME] | [NAME] | Marianne | [NAME] | [NAME]
 |  | Third | [NAME] | Sheena | [NAME] | Fanny | Susan2
 |  | Fourth | Anne | [NAME] | David1 | [NAME] | [NAME]
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Bracket Counts
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ( | )
 |  | The 1st formula gets the (absolute) row number along which there is a match |  |  |  |  |  |  |  | IF($J$20="","",IFERROR(IF(ROW(INDEX($C$1:$C$24,MAX(IF(ISNUMBER(SEARCH("/"&$J$20&"/","/"&$D21:$H21&"/")),ROW(21:21),0),0),1))<ROW(21:21),"",ROW(INDEX($C$1:$C$24,MAX(IF(ISNUMBER(SEARCH("/"&$J$20&"/","/"&$D21:$H21&"/")),ROW(21:21),0),0,1)))),"-"))
 |  | This formula is dragged down from ROW(21:21) to ROW(24:24)
 |  |  |  |  |  |  |  |  |  | IF($J$20="",
 |  |  |  |  |  |  |  |  |  |  | "",
 |  |  |  |  |  |  |  |  |  |  | IFERROR(
 |  | The IF statement is necessary because without it a 1 is returned for no-match. |  |  |  |  |  |  |  |  |  | IF(
 |  | I do not really know why! |  |  |  |  |  |  |  |  |  |  | ROW(
 |  |  |  |  |  |  |  |  |  |  |  |  |  | INDEX( | $C$1:$C$24,
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | MAX(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | IF(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ISNUMBER(
 |  | The search string and the searched array are enclosed with "/" to exclude spurious |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | SEARCH("/"&$J$20&"/","/"&$D21:$H21&"/")
 |  | matches with names like "[NAME], Ann, [NAME], Anne", all containing "ann". |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ROW(21:21),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1
 |  |  |  |  |  |  |  |  |  |  |  |  |  | )
 |  |  |  |  |  |  |  |  |  |  |  |  | )<ROW(21:21),
 |  |  |  |  |  |  |  |  |  |  |  |  | "",
 |  |  |  |  |  |  |  |  |  |  |  |  | ROW(
 |  |  |  |  |  |  |  |  |  |  |  |  |  | INDEX( | $C$1:$C$24,
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | MAX(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | IF(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ISNUMBER(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | SEARCH("/"&$J$20&"/","/"&$D21:$H21&"/")
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ROW(21:21),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1
 |  |  |  |  |  |  |  |  |  |  |  |  |  | )
 |  |  |  |  |  |  |  |  |  |  |  |  | )
 |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  | "-"
 |  |  |  |  |  |  |  |  |  |  | )
 |  |  |  |  |  |  |  |  |  | )
 |  | Compare Methods 1 and 2 (matrix and vector approach)
 |  | ISNUMBER(SEARCH("/"&$J$20&"/","/"&$D$21:$H$24&"/")) |  |  |  |  |  | IF(ISNUMBER(SEARCH($J$20,$D$21:$H$24)),ROW($21:$24),0) |  |  |  |  |  |  |  | SMALL(IF(ISNUMBER(SEARCH($J$20,$D$21:$H$24)),ROW($21:$24)),ROW(*:*))
 |  |  | False | False | False | False |  |  | 0 | 0 | 0 | 0
 |  | False | True | False | True | True |  | 0 | 22 | 0 | 22 | 22
 |  | True | False | False | False | False |  | 23 | 0 | 0 | 0 | 0
 |  | False | False | True | False | False |  | 0 | 0 | 24 | 0 | 0
 |  | ISNUMBER(SEARCH("/"&$J$20&"/","/"&$D21:$H21&"/")) |  |  |  |  |  | IF(ISNUMBER(SEARCH("/"&$J$20&"/","/"&$D21:$H21&"/")),ROW(21:21),0) |  |  |  |  |  |  |  | MAX(IF(ISNUMBER(SEARCH("/"&$J$20&"/","/"&$D21:$H21&"/")),ROW(21:21),0),0)
 |  |  | False | False | False | False |  |  | 0 | 0 | 0 | 0
 |  | ISNUMBER(SEARCH("/"&$J$20&"/","/"&$D22:$H22&"/")) |  |  |  |  |  | IF(ISNUMBER(SEARCH("/"&$J$20&"/","/"&$D22:$H22&"/")),ROW(22:22),0) |  |  |  |  |  |  |  | MAX(IF(ISNUMBER(SEARCH("/"&$J$20&"/","/"&$D22:$H22&"/")),ROW(22:22),0),0)
 |  |  | True | False | True | True |  |  | 22 | 0 | 22 | 22
 |  | ISNUMBER(SEARCH("/"&$J$20&"/","/"&$D23:$H23&"/")) |  |  |  |  |  | IF(ISNUMBER(SEARCH("/"&$J$20&"/","/"&$D23:$H23&"/")),ROW(23:23),0) |  |  |  |  |  |  |  | MAX(IF(ISNUMBER(SEARCH("/"&$J$20&"/","/"&$D23:$H23&"/")),ROW(23:23),0),0)
 |  |  | False | False | False | False |  |  | 0 | 0 | 0 | 0
 |  | ISNUMBER(SEARCH("/"&$J$20&"/","/"&$D24:$H24&"/")) |  |  |  |  |  | IF(ISNUMBER(SEARCH("/"&$J$20&"/","/"&$D24:$H24&"/")),ROW(24:24),0) |  |  |  |  |  |  |  | MAX(IF(ISNUMBER(SEARCH("/"&$J$20&"/","/"&$D24:$H24&"/")),ROW(24:24),0),0)
 |  |  | False | True | False | False |  |  | 0 | 24 | 0 | 0
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Bracket Counts
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ( | )
 |  | The 2nd formula sorts the (absolute) row numbers so that blank lines are omitted |  |  |  |  |  |  |  |  | IFERROR(INDEX($K$21:$K$24,SMALL(IF(LEN($K$21:$K$24),ROW(INDIRECT("1:"&ROWS($K$21:$K$24)))),ROW(1:1)),1),"")
 |  | This formula is dragged down from ROW(21:21) to ROW(24:24)
 |  |  |  |  |  |  |  |  |  |  | IFERROR(
 |  |  |  |  |  |  |  |  |  |  |  | INDEX( | $K$21:$K$24,
 |  |  |  |  |  |  |  |  |  |  |  |  | SMALL(
 |  |  |  |  |  |  |  |  |  |  |  |  |  | IF(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | LEN($K$21:$K$24),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ROW(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | INDIRECT("1:"&ROWS($K$21:$K$24))
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | )
 |  |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  |  | ROW(1:1)
 |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  | 1
 |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  | ""
 |  |  |  |  |  |  |  |  |  |  | )
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Bracket Counts
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ( | )
 |  | The 3rd formula returns the item name from the row number in the sorted column |  |  |  |  |  |  |  |  |  | IFERROR(INDEX($C$1:$C$24,$L21,1),"")
 |  | This formula is dragged down from ROW(21:21) to ROW(24:24)
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Bracket Counts
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ( | )
 |  | The 4th formula searchs along the row number in the sorted column for all occurrences of the name |  |  |  |  |  |  |  |  |  |  | IF($M21="","",IFERROR(INDEX(INDIRECT("$A$"&$L21&":$H$"&$L21),1,SMALL(IF(ISNUMBER(SEARCH("/"&$J$20&"/","/"&INDIRECT("$D$"&$L21&":$H$"&$L21)&"/")),COLUMN($D:$H)),COLUMN(A:A))),"-"))
 |  | This formula is dragged across the row (ALWAYS ROW 1) from COLUMN(A:A) to COLUMN(E:E)
 |  | The INDIRECT statement allows constant array Columns, and variable row when dragged down |  |  |  |  |  |  |  |  |  |  | IF($M21="",
 |  | (because the arguments of INDIRECT are enclosed in text marks),so drag from $[NAME] to $L24 |  |  |  |  |  |  |  |  |  |  |  | "",
 |  |  |  |  |  |  |  |  |  |  |  |  |  | IFERROR(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | INDEX(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | INDIRECT("$A$"&$L21&":$H$"&$L21),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1,
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | SMALL(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | IF(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ISNUMBER(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | SEARCH("/"&$J$20&"/","/"&INDIRECT("$D$"&$L21&":$H$"&$L21)&"/")
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | COLUMN($D:$H)
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | COLUMN(A:A)
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | )
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  |  | "-")
 |  |  |  |  |  |  |  |  |  |  |  |  | )