google-sheetsgoogle-sheets-formula
Can a formula return corresponding values from one column into another, removing blanks and separating them with commas?
I have a sheet with the following setup:
![![Spreadsheet Image]](https://i.sstatic.net/z1IcXly5.png)
In cell K2, I use this formula:
=IF(K1="","",IF(XLOOKUP(K1,H:H,I:I)=0,"",TRANSPOSE(FILTER(B1:F1,ISNUMBER(SEARCH(TRIM(K1),QUERY(B2:F,,ROWS(B2:F))))))))
This formula does the following:
- Checks if K1 is not blank.
- Searches the dataset B2:F for the value in K1.
- Returns the corresponding results from B1:F1, removing blanks.
- Ensures that the value in column I is not 0.
I want to extend this formula to also return the corresponding values from column A2:A and display these results in L2:L, separated by commas if there are multiple matches.
Is it possible to modify the existing formula to include this, or would it be better to use a separate formula in L2?
Example of expected results:
Link to spreadsheet: https://docs.google.com/spreadsheets/d/1tprQIYEMgLRuyCMCQ0cDO_JDzWKdW-eTyXH76HJTeic
Solution
Here's a generalized approach which you may adapt accordingly:
=let(Λ,transpose(bycol(B2:F,lambda(Σ,if(xmatch(K1,Σ),vstack(index(1:1,column(Σ)),join(", ",filter(A2:A,Σ=K1))))))),
filter(Λ,index(Λ,,2)<>""))
- How do I compare new IDs with a column of existing IDs and allowcate a timestamp if two IDs match on Google Sheets?
- Google script Display array with rows of different sizes
- List rows in array when the cell contain number in the string
- ImportXML XPath issue using Google Sheets on a simple web scraping query
- Return index of cell that contains match on two strings
- Conditional formatting if cell past date and another cell empty with arrayformula
- How to find the Second Space from the right in Google Sheets?
- Joining more than two WHERE statements in Query Language
- Python gspread create sheet if not exist
- Find if cell contents exist in array
- Google Apps Script: Finding an element in a Column and returning the corresponding row
- Active VS Effective User for Google Sheets
- Rearrange data in table format to rows
- I am not able to extract URL from these cell values using Google Apps Script
- How to count a specific word from a string in a cell from the entire column in google sheets
- Error returned by action script when calling an API with UrlFetchApp.fetch
- How can I make this formula more dynamic by including another criteria (cell F11) in the query?
- How to execute a Google Apps Script ONLY on the edited row
- How to copy the last row in one table and append to another table in the same GSheet (the same tab)
- Replace duplicate row with new data in Google Sheets using Apps Script
- In Google sheets, how does one either return a blank cell or perform a function based on another cells lack of content?
- Incorrect result of iferror formula via Apps Script
- How to Extract URLs from HYPERLINK in Google Sheets
- Using a reference cell as part of a column in a google sheets query
- Can a formula return corresponding values from one column into another, removing blanks and separating them with commas?
- Google Finance does not show last closing price after market hours. It just shows last *traded* price instead of *closing*
- How to ignore empty rows when submitting to database
- Using Named Functions with New Google Tables
- Google sheets query not returning words with special characters
- Adding multiple durations together in google sheets breaking