CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formulaspreadsheet
Zediiiii
Zediiiii

Reputation: 780

Multiple Match Results From Array Search

For the sake of MWE, I have an array in $AP$4:$BO$20 with a single string in each cell. The data in each cell is an alphanumeric code, such as 1,1a,2b,3c, etc.

Row 22, starting in column AQ, contains a single string that matches one or more of the strings in the array named above. Goal: using each string in AQ22:AO22, create a formula that extracts EVERY row number of the cells in the array $AP$4:$BO$20 that contain exactly the value in AQ22:AO22.

Bonus for doing it without using an array formula. VBA is not an option since this is Google Sheets, and I'd really prefer to avoid g-apps-script.

I've attempted using 

  =INDIRECT(ADDRESS(MIN(IF(NOT(ISERROR(FIND(AQ22,$AP$4:$BO$20,1))),ROW($AP$4:$BO$20),"")),1))

and

=IFERROR(INDEX($AP$4:$BO$20,SMALL(IF($AP$4:$BO$20=AQ22,ROW($AP$4:$BO$20)-4),ROW(A1)),2),"")

and even the illustrious

=IF(ISERROR(INDEX($AP$4:$BO$20,SMALL(IF($AP$4:$BO$20=AQ22,ROW($AP$4:$BO$20)),ROW(1:1)),2)),"",INDEX($AP$4:$BO$20,SMALL(IF($AP$4:$BO$20=AQ22,ROW($AP$4:$BO$20)),ROW(1:1)),2))

Here is a toy sheet to test out ideas with using this information. Note the comment on the cell where the formula will begin.

Upvotes: 0

Views: 376

Answers (3)

Zediiiii
Zediiiii

Reputation: 780

Though this is a very specific application in response to this question, for the sake of the knowledge base, I'd like to show how I dealt with an instance of multiple match values. There is likely a much better way, but here is one way.

To give this context, imagine the LIST_CELL is a list of question numbers (which are entered in as a header row, call the range QUESTIONS) on a test that correspond to certain standards, and the goal is to average only the questions that correspond to the standard next to which the list is written, and for each student. Using

=iferror(join(",",ArrayFormula(match(split(LIST_CELL,","),QUESTIONS,FALSE))),"")

The split function splits the a hand-entered list of questions on commas, the match function returns the column number of that particular question in QUESTIONS, and the join function joins the data back together. ArrayFormula allows the match to be performed on an array instead of just the first value.

Another single row heading lists the standards to which each question has been matched (possibly to more than one standard) by the comma separated list in LIST_CELL. For a column list of students in A:A, each standard needs to average the scores of every question that is listed next to the standard. This is accomplished by the nifty (if clunky):

average(ArrayFormula(hlookup(split(vlookup(LOOKUP_VAL,SEARCH_RANGE,COL_W_LIST),","),DATA_SOURCE,row(CURRENT_CELL))))

Breakdown from center outward:

LOOKUP_VAL is the value being looked up (the one that has multiple matches); in the example context, it's the standard.

SEARCH_RANGE is a range of cells containing both the list of lookup value (the standards in context) and the comma separated lists of column numbers generated by the first function. COL_W_LIST is the column number in the array SEARCH_RANGE that contains the list of row numbers matched from LIST_CELL.

Split takes the elements apart and placed them in a temporary array so that hlookup can be performed on each element. Via ArrayFormula the hlookup grabs each value on the same row in the appropriate QUESTIONS column - in context, it grabs the point scores for each question matched to the standard.

Finally, average is self-explanatory, and does take an array as input apparently.

These two functions in combination allow of use of indirect cell references in an array formula, and solves the much asked, "how do I include multiple matches in a calculation" question. At least in this specific context.

Upvotes: 1

Slai
Slai

Reputation: 22866

Not sure if I understand the desired result correctly, but 

= IfError( Filter( Row($AL$4:$AL$16), RegExMatch( $AL$4:$AL$16, "\b" & AQ22 & "\b" ) ), "")

results in 7, and 9 in a separate cell below it. \b is a word boundary that matches between alphanumeric and non-alphanumeric character. If you want the result in one cell, you can join them:

=IfError(Join(",", Filter(Row($AL$4:$AL$16), RegExMatch($AL$4:$AL$16, "\b"&AQ22&"\b"))), "")

You can also match multiple values:

=IfError(Filter(Row($AL$4:$AL$16), RegExMatch($AL$4:$AL$16, "\b(" & Join("|", AQ22:AZ22) & ")\b")), "")

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

In Excel you can do it without a CSE entered formula, but I don't know if the AGGREGATE function is available in Sheets:

=IFERROR(AGGREGATE(15,6,1/((AQ$22=arr)*(LEN(arr)>0))*ROW(arr),ROWS($1:1)),"")

For an array entered formula:

=IFERROR(SMALL(IF((AQ$22=arr)*(LEN(arr)>0),ROW(arr),""),ROWS($1:1)),"")

For either one of those formulas, enter in AQ24, then fill down until you get blanks, and across. When you fill down, none of the "target rows" can be hidden (or else the result of the formula will be hidden).

arr refers to $AP$4:$BO$20

Upvotes: 1

Related Questions