Google Sheets – replace filter with vlookup for array formula

Cor blimey, this one was a toughy to get to the bottom of! No amount of googling brought me the solution I needed, however by combining resources from three/four places I finally got there.

http://chandoo.org/wp/2014/10/28/multi-condition-vlookup/

http://codingisforlosers.com/learn-vlookup-formula-google-sheets/

https://stackoverflow.com/questions/22260953/google-spreadsheet-vlookup-with-arrayformula-plus-multiplication-of-returned-val

https://productforums.google.com/forum/#!searchin/docs/parallel$20lookup$20solution/docs/36A0epDlIdE

Now the filter function in Google Sheets is a lovely thing, but it doesn’t play nicely if you want an array formula, that is a formula that automagically fills down as you add more data to a sheet. So you have to convert over to using vlookup (or other method) to get the result you want. Now on the face of it vlookup will only handle one criteria, but it can be forced, with a fair bit of jiggery pokery to handle multiple criteria, and then work as an array formula.

Here was the problem.

On one tab on a google sheet, I had data entry regarding attendance (or not) to training events. Simple enough, course, date, name, and a Yes/No for attendance. Easy list.

attdb

On another sheet I was creating a report, based upon other data, telling me if a person needed to have training in a particular area. This was fine, returning me a list of names, based upon the selection of a course, from another set of data (maybe more on this one later, as this also posed its own problems!). What I want to show next to the names on this list, the date they had last attended the training for the selected course.

staffdb

Now the filter function was an easy one, coping quite happily for the three criteria, but only for one cell:

I did not want to copy this down, as the number of names could go above the 200 mark, or be as few as 5. What I wanted was an array formula!

In order to make vlookup work, I had to concatenate the three values I wanted as criteria into the “search_key”, the for the “range”, use an inner array formula to concatenate the ranges needed – in the same order, along with the return range all in an outer array formula (curly braces {}), then the “index” and “sort_option” much as for a normal vlookup. Take this, and wrap it all up in an array formula again (CTRL+SHIFT+ENTER). Also add in an “iferror” function to make unwanted lines go away. For array formulas to work you have include ranges whereever needed – e.g. A2:A instead of just A2.

Here is the corresponding vlookup command that does the same as the filter:

wnw2

One thought on “Google Sheets – replace filter with vlookup for array formula

  1. Pingback: GAS – Google Sheets – Query the whole table ? No you can’t ! | METRIC RAT

Leave a Reply

Your email address will not be published. Required fields are marked *