google-sheetsspreadsheetarray-formulasindex-match
Combining INDEX(MATCH()) with ARRAYFORMULA() fails
I need a formula for the VariantAttribute column, which fills in the ProductAttribute value based on the first 4 characters of the VariantID
Desired result:
| ProductID | ProductAttribute | VariantID | VariantAttribute | |
|---|---|---|---|---|
| ABCD | blue | ABCD-xx | blue | |
| BCDE | black | ABCD-yy | blue | |
| CDEF | orange | BCDE-vv | black | |
| DEFG | blue | BCDE-ww | black | |
| CDEF-uu | orange | |||
| DEFG-zz | blue | |||
| ABCD-uu | blue |
I tried to combine ARRAYFORMULA() with INDEX(MATCH())but failed, obviously because I'm not able to specify a search range within ARRAYFORMULA()
How can I get the desired result?
Solution
Assuming the dash ("-") is consistent.
=ARRAYFORMULA(IF(D2:D="",,VLOOKUP(INDEX(SPLIT(D2:D,"-"),,1),A:B,2,0))
- How to extract url from Google Sheet Cell with anchor text hyperlink
- Function onEdit(e) works only for single cell edit
- Cancel Script is a cell is RED
- Can we hide column in sheet using google apps script?
- JavaScript function in Google spreadsheets to send QR codes to emails
- Google Sheet Concatenate + Filter
- Google Sheets QUERY is returning with a no column found errors
- Get eventID for Google Calendar Event
- How can I find the number of concordant/discordant pairs for a given cell in Google Sheets?
- COUNTIF Statements: Range Across All Sheets + Cell Reference as Criterion
- Writing a googlefinance wrapper
- Getting a macro to run using checkbox in google sheets
- Auto expanding formula in google sheets to the length of a dynamic range
- Issue in Auto close Modal Dialog - Google App-Script
- CSV extracted automatically into a sheet. Need help for filtering
- How can I sum top 5 values from column in Google Sheets
- Remove duplicates based on one column and keep latest entry in Google sheets (while ignoring blank entries in that column)
- Find the list of different values in 2 tables by id key
- VAT Checker Apps Script
- Script to lock a different tab sheet everyday
- Trigger python code from Google spreadsheets?
- Function to convert Google Sheet Table into one row
- Google Sheet Script 2 on edit function #NUM! error
- Google Sheet SUMIF returning an error! statement
- Same code behaving differently inside and outside function
- How to use VLOOKUP() + QUERY() + IMPORTRANGE() with multiple conditions?
- How to find value vertically and horizontally, using formula in Sheets?
- Return SUM of all cells that fit TWO CRITERIA related by POSITION
- Sum column until first empty cell
- Extract Data from Multiple Sheets with Similar Names