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 2

Locate a given input string in an array and list the item associated with the row that string occupies (Solution 2)
 |  |  |  |  |  |  |  |  |  | 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(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
 |  |  |  |  |  |  |  |  |  |  |  |  | )
 |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  | "-")
 |  |  |  |  |  |  |  |  |  | )
 |  | The effect of component statements is:
 |  | ISNUMBER(SEARCH("/"&$J$20&"/","/"&$D$21:$H$24&"/") |  |  |  |  |  | IF(ISNUMBER(SEARCH("/"&$J$20&"/","/"&$D$21:$H$24&"/")),ROW($21:$24),0) |  |  |  |  |  |  |  | IFERROR(SMALL(IF(ISNUMBER(SEARCH("/"&$J$20&"/","/"&$D$21:$H$24&"/")),ROW($21:$24)),ROW(1:1)),"Error")
 |  |  | 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
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Bracket Counts
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ( | )
 |  | The 2nd formula sorts the (absolute) row numbers so that blank and duplicate lines are omitted |  |  |  |  |  |  |  |  | IFERROR(INDEX($K$21:$K$26, MATCH(SMALL( IF(LEN($K$21:$K$26)=0, "", IF(COUNTIF(L$20:L20, $K$21:$K$26)=0, IF(ISNUMBER($K$21:$K$26), COUNTIF($K$21:$K$26, "<"&$K$21:$K$26), COUNTIF($K$21:$K$26, "<"&$K$21:$K$26)+SUM(1*ISNUMBER($K$21:$K$26))+1), "")), 1),IF(LEN($K$21:$K$26)=0, "", IF(COUNTIF(L$20:L20, $K$21:$K$26)=0, IF(ISNUMBER($K$21:$K$26), COUNTIF($K$21:$K$26, "<"&$K$21:$K$26), COUNTIF($K$21:$K$26, "<"&$K$21:$K$26)+SUM(1*ISNUMBER($K$21:$K$26))+1), "")), 0)), "")
 |  | This formula is dragged down from ROW(21:21) to ROW(26:26) (or further)
 |  |  |  |  |  |  |  |  |  |  | IFERROR(
 |  |  |  |  |  |  |  |  |  |  |  | INDEX( | $K$21:$K$26,
 |  | Note that duplicates are removed as L$20:L20 drags down ti L$20:L$25 |  |  |  |  |  |  |  |  |  |  | MATCH(
 |  |  |  |  |  |  |  |  |  |  |  |  |  | SMALL(
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | IF( | LEN($K$21:$K$26)=0,
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | "",
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | IF( | COUNTIF(L$20:L20, $K$21:$K$26)=0,
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | IF( | ISNUMBER($K$21:$K$26),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | COUNTIF($K$21:$K$26, "<"&$K$21:$K$26),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | COUNTIF($K$21:$K$26, "<"&$K$21:$K$26)+SUM(1*ISNUMBER($K$21:$K$26))+1
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ""
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | )
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1
 |  |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  |  | IF( | LEN($K$21:$K$26)=0,
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | "",
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | IF( | COUNTIF(L$20:L20, $K$21:$K$26)=0,
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | IF( | ISNUMBER($K$21:$K$26),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | COUNTIF($K$21:$K$26, "<"&$K$21:$K$26),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | COUNTIF($K$21:$K$26, "<"&$K$21:$K$26)+SUM(1*ISNUMBER($K$21:$K$26))+1
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ""
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | )
 |  |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 0
 |  |  |  |  |  |  |  |  |  |  |  |  | )
 |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  | ""
 |  |  |  |  |  |  |  |  |  |  | )
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 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)
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | )
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ),
 |  |  |  |  |  |  |  |  |  |  |  |  |  | "-")
 |  |  |  |  |  |  |  |  |  |  |  |  | )