How to use VLOOKUP() + QUERY() + IMPORTRANGE() with multiple conditions?
I currently have this one working fine with 01 condition, but I can't manage to include another criteria:
=Arrayformula(if(A3:A="","",IFNA(vlookup(E3:E,QUERY(IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU","Arrayformula VLOOKUP multiple columns!A1:C"), "select Col1, Col2, Col3 label Col3 'Qtd'",0),3,0))))
I've started by concatenating A3:A&E3:E, but I don't know how to get the data ranges for comparison in this fashion using QUERY().
Here is the result VS expectation:
Also, here is an example for tests: https://docs.google.com/spreadsheets/d/1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU/edit?usp=sharing
Solution
use:
=ARRAYFORMULA(IF(A3:A="",,IFNA(VLOOKUP(E3:E&" "&F3:F,
QUERY({FLATTEN(QUERY(TRANSPOSE(
IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU", "Arrayformula VLOOKUP multiple columns!A1:B")),,9^9)),
IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU", "Arrayformula VLOOKUP multiple columns!C1:C")},
"select Col1,Col2 label Col2 'x'", 0), 2, 0))))
- How can I use Google Sheets Apps Script to Auto Generate today's date when an edit is made to a given column?
- IsDate ArrayFormula
- how to show instruction with image after install google workspace extension
- Formula to convert matrix to rows in Google Sheets
- Regular expression to extract valid cell references from a spreadsheet formula
- Is Google Sheets less or more accurate than Microsoft Excel?
- google sheet merge two column into one column with prefix words
- Get highest occurring row number of key?
- If checkbox checked set values
- Clarification on using the forEach() function
- Why is this ARRAYFORMULA + VLOOKUP() + REGEXEXTRACT() with multiple conditions not working?
- How can I make this formula more dynamic by including another criteria (cell F11) in the query?
- Add title row with ARRAYFORMULA in Google Sheets
- Rank based on which column is being sorted by in a Table
- Auto border to a Dynamic Data
- How can I determine if a respondent's email address is unique in a list?
- Combine IF for text with INDEX and MATCH? Or VLOOKUP? QUERY?
- Google Sheets - returning Nth result with multiple matching criteria
- Can a label in a Google Sheets Query be dynamic; can it reference a variable?
- Sum a number of range values; process and add to array
- Downloading a PDF version of the open spreadsheet in Google Drive via scripts
- exporting spreadsheet to pdf then saving the file in google drive
- How to get last number in a row on Google Spreadsheets
- A Script to Simplify Creating a SO Table
- Archive a row after a dropdown status is selected on multiple sheets
- How to extract URL from Link in Google Sheets using a formula?
- Create list of dates in Google Sheets based on day-of-month schedule
- Get Google Sheet by ID?
- Return Column Based on Column Headers
- Why does my Google Service Account not see files shared with it, but can see files created with it?