'How can I search a Google Sheet range row-by-row for matches to headers on another sheet and then add the first cell of the row under the header?

I have made a mockup of my google sheet here: https://docs.google.com/spreadsheets/d/1u5wlCjSdZOYdAs0V-WKGK8KYcZ7p80OJReTasTK3oH4/edit?usp=sharing. (My actual data consists of 90 columns and 60 rows.)

I want to search the range data!B2:M6 row-by-row for matches to formula!A1:L1 and if, for example, a match to formula!A$1 is found in the range data!B2:M2, I want data!$A2 to be placed in the first empty space in column A, etc.

Thank you so much for the assistance.

addendum: A complication it would be nice to solve as well. Some data is not an exact match, missing the final character (so it's Chemistr instead of Chemistry).

arraysgoogle-sheetsgoogle-sheets-formulatransposeflatten


Solution 1:[1]

try:

=INDEX(IFERROR(HLOOKUP(A19:L19, TRANSPOSE(SPLIT(FLATTEN(QUERY(QUERY(SPLIT(FLATTEN(
 IF(data!B2:M="",,data!B2:M&"×"&data!A2:A)), "×"), 
 "select max(Col2) where Col2 is not null group by Col2 pivot Col1"),,9^9)), " ")), 
 {2; 3; 4; 5; 6}, 0)))

enter image description here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 player0

Related Questions