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 1

Locate a given input string in an array and list the item associated with the row that string occupies (Solution 1)
 |  |  |  |  |  |  |  |  |  | improvement possible:
 |  |  |  |  |  |  |  |  |  | restrict to unique item result when name appears twice on a row
[NAME]: |  | Match all Items where a given name occurs, and all the times it occurs
 |  |  |  |  |  |  |  |  |  | Unsorted
 |  | Item | Name 1 | Name 2 | Name 3 | Name 4 | Name 5 | Name= | *avi* | 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(ROW(INDEX($C$1:$C$24,SMALL(IF(ISNUMBER(SEARCH("/"&$J$20&"/","/"&$D$21:$H$24&"/")),ROW($21:$24)),ROW(1:1)),1)),""))
 |  | This formula is dragged down from ROW(21:21) to ROW(26:26) (or further)
 |  |  |  |  |  |  |  |  |  | IF($J$20="",
 |  |  |  |  |  |  |  |  |  |  | "",
 |  | The search string and the searched array are enclosed with "/" to exclude spurious |  |  |  |  |  |  |  |  | IFERROR(
 |  | matches with names like "[NAME], Ann, [NAME], Anne", all containing "ann". |  |  |  |  |  |  |  |  |  | ROW(
 |  |  |  |  |  |  |  |  |  |  |  |  | INDEX( | $C$1:$C$24,
 |  |  |  |  |  |  |  |  |  |  |  |  |  | SMALL(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | IF(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ISNUMBER(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | SEARCH("/"&$J$20&"/","/"&$D$21:$H$24&"/")
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | [NAME])
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ROW(1:1)
 |  |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 1
 |  |  |  |  |  |  |  |  |  |  |  |  | )
 |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  | "-")
 |  |  |  |  |  |  |  |  |  | )
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Bracket Counts
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ( | )
 |  | The 2nd formula returns the item name from the row number in the unsorted column |  |  |  |  |  |  |  |  |  | IFERROR(INDEX($C$1:$C$24,$K21,1),"")
 |  | This formula is dragged down from ROW(21:21) to ROW(26:26)
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Bracket Counts
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ( | )
 |  | The 3rd formula searchs along the row number in the unsorted column for all occurrences of the name |  |  |  |  |  |  |  |  |  |  | IF($M21="","",IFERROR(INDEX(INDIRECT("$A$"&$K21&":$H$"&$K21),1,SMALL(IF(ISNUMBER(SEARCH("/"&$J$20&"/","/"&INDIRECT("$D$"&$K21&":$H$"&$K21)&"/")),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 $K21 to $[NAME] |  |  |  |  |  |  |  |  |  |  |  | "",
 |  |  |  |  |  |  |  |  |  |  |  |  |  | IFERROR(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | INDEX(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | INDIRECT("$A$"&$K21&":$H$"&$K21),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1,
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | SMALL(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | IF(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ISNUMBER(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | SEARCH("/"&$J$20&"/","/"&INDIRECT("$D$"&$K21&":$H$"&$K21)&"/")
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | COLUMN($D:$H)
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | COLUMN(A:A)
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | )
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  |  | "-")
 |  |  |  |  |  |  |  |  |  |  |  |  | )