Search multiple columns and return top cell value

1
votes

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

1
google-sheetsarray-formulasgoogle-sheets-formulagoogle-sheets-querygs-vlookup
In principle you can use Hlookup or Vlookup as an alternative to Index, but the problem is more that Max doesn't work with array formulas either, and that you're already working with a 2d array. - Tom Sharpe
@player0 I edited the question to include a link to my sheet - TC76
@TomSharpe I don’t think I necessarily need MAX. Seems like ARRAYFORMULA will automatically fill to the bottom row anyway. As I understand it - TC76
subTaskData!C2 (edited in question) - TC76

1 Answers

1
votes

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