arraysgoogle-sheetsgoogle-sheets-formulatransposeflatten
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).
Solution
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)))
- Why is the spread operator needed for Math.max()?
- How to access multidimensional data from a json file?
- How to access multidimensional data with loops
- Difference between np.nan and np.NaN
- JS: Filter array of objects by max value per category
- Bash array with spaces in elements
- How to display a multidimensional array
- How to join / combine two arrays to concatenate into one array?
- How do I find a partial match from a data table to an individual cell?
- Parsing a string representation of nested arrays into an Array
- Pipe complete array-objects instead of array items one at a time?
- In Rust, is there any reason to use a `const` array vs array reference?
- How to set a Rust array length dynamically?
- Swift async Loop in Task
- How to print a bash array on the same line
- Looped process only shows the first result per iteration
- How to access a column value in a 2d array?
- Format 2d array data into a string of hyphenated consecutive day ranges based on a shared time range
- Create an array with same element repeated multiple times
- Difference between List and Array types in Kotlin
- Python list with NumPy arrays as elements
- SwiftUI Mapkit: How to reference values from an array for markers?
- How do I Merge two Arrays in VBA?
- Sum of corresponding values from different arrays of the same size with Python
- 2D array in YAML
- Dynamic multidimensional array in twig
- How to append something to an array?
- How does PHP determine the key to use if I declare array elements without offering explicit keys?
- searching an unordered integer array
- PHP JSON (decode)