CODEWITHSUNDEEP
google-sheets
Rowan
Rowan

Reputation: 1

Google Sheets filter() wrapped in arrayformula() without vlookup()

Reference/test sheet: https://docs.google.com/spreadsheets/d/1fp6ZTBtgb5E0J9GKOqh8Ae47OzY1smec5ha9BfUfAsY/edit?usp=sharing

I have a Google Sheets document with one sheet (calculator) that pulls some values from another sheet (database). database is organized by two columns: make, and model. I use some weird data validation and helper columns to make dropdowns in calculator. Then I use filter() to pull the matching value from database.

This all works fine but it will be a calculator that gets reused and the data discarded, so I need only a finite number of rows in calculator (10-20). For this, it would be super nice to be able to select the whole row and hit delete to clear the calculation without destroying all the formulas. Ideally, the filter() would happen inside an arrayformula() in a hidden and protected top row to allow the rows to be easily cleared.

For some reason though, I can't get that to work. vlookup() is not an option because I need to match two keys.

On another note, it would be nice to not need the helper columns B:J and the data validation unique to each row. This is workable though as I only need a few rows. In the actual version I hide and protect B:J and there are many more columns there.

Upvotes: 0

Views: 179

Answers (2)

kirkg13
kirkg13

Reputation: 3010

You can't get rid of the helper column approach, as long as you want the calculator to use drop down selection for the model. Data validation for dropdowns requires either a list of values, ie. static, so no good, or a range of cells.

What you might want to do is to put those cells in a totally separate tab, eg. DataValidation, and then hide that tab. Your Calculator sheet will then be cleaner, with no hidden columns. Column K will use for data validation the "hidden" values, formerly columns B:J, that are now built off in the DataValidation tab.

Upvotes: 0

Dimension Datacraft
Dimension Datacraft

Reputation: 136

I know you said Vlookup() wouldn't work because you need to match on two keys, I think that vlookup() will help in this situation. Try this formula...

=arrayformula(Vlookup(A3:A&K3:K,{database!A$2:A&database!B$2:B,database!C$2:C},2,False))

The concept here is to put those two matches you need into one unique key. So we use the curly brackets {} to build an array within the formula and combine those two lookup fields in your 'database' sheet. So the columns of A and B become concatenated into one element, and the second part of that array is the column C which you need.

To lookup then just combines your A&K columns similarly, so it can lookup that combined element. The rest of the vlookup follows as normal. I.e. we look up this concatenation against that one and when it matches it returns the second column of the array we built, in this case database!C.

I don't think I'm clear on your columns B:J, so I'm not sure if this helps you with that as well.

Upvotes: 0

Related Questions