CODEWITHSUNDEEP
google-sheetsarray-formulasgoogle-sheets-formulagoogle-sheets-querygs-vlookup
TC76
TC76

Reputation: 860

Search multiple columns and return top cell value

I have an INDEX formula that will take a value from the first cell in a row, search multiple columns of another sheet for a match and return the corresponding header row value when the match is found.

The problem is that I have to manually copy this formula down in order for it to apply to every row. But more to the point, if I edit the formula or create new rows, I need to make sure I apply that edit to every row. I would much rather have a single formula at the top of the column that applies to all cells below.

=INDEX(subTaskCategories!$C$1:$J$1,MAX(IF(subTaskCategories!$C$2:$J=A2,COLUMN(subTaskCategories!$C$2:$J)-COLUMN(A2)-1)))

What is an alternative to INDEX that will search multiple columns and return the header when a match is found that can also be wrapped in an ARRAYFORMULA?

My sheet in subTaskData!C2

Upvotes: 1

Views: 466

Answers (1)

player0
player0

Reputation: 1

paste in B2 cell:

=ARRAYFORMULA(IFERROR(VLOOKUP(REGEXEXTRACT(C2:C, "^~ (.*) ~$"), 
 TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(
 QUERY(TRANSPOSE(IF(INDIRECT("taskCategories!C2:"&ADDRESS(ROWS(taskCategories!A1:A), 
 COLUMNS(taskCategories!A1:AA1), 4))<>"", "♥"&INDIRECT("taskCategories!C2:"&
 ADDRESS(ROWS(taskCategories!A1:A), COLUMNS(taskCategories!A1:AA1), 4))&"♦"&
 INDIRECT("taskCategories!C1:"&ADDRESS(1, COLUMNS(taskCategories!A1:AA1), 4)), ))
 , , 999^99)), , 999^99), "♥")), "♦")), 2, 0)))

0

paste in C2 cell:

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(
 QUERY(TRANSPOSE(IF(INDIRECT("subtaskCategories!C2:"&ADDRESS(ROWS(subTaskCategories!A1:A), 
 COLUMNS(subTaskCategories!A1:AA1), 4))<>"", "♥"&INDIRECT("subtaskCategories!C2:"&
 ADDRESS(ROWS(subTaskCategories!A1:A), COLUMNS(subTaskCategories!A1:AA1), 4))&"♦"&
 INDIRECT("subtaskCategories!C1:"&ADDRESS(1, COLUMNS(subTaskCategories!A1:AA1), 4)), ))
 , , 999^99)), , 999^99), "♥")), "♦")), 2, 0)))

0

Upvotes: 1

Related Questions