Lookup or Filter data using Multiple Criteria

Problem

  • How to lookup or filter data using multiple criteria?
  • How to extract certain set of rows based on multiple conditions? :thinking:

Some of the ways to tackle this over a spreadsheet are -

  1. Build a complex and overtly unnecessary INDEX/MATCH function, wrapped around an ARRAYFORMULA :woman_facepalming:t4: -

  1. Use the plain ol’ reliable FILTER function :mechanical_arm: -

You may’ve even considered using the more advanced QUERY function as well, given all the options :man_shrugging:t4: or may be used some helper columns to try and make the row items unique - these columns have their moments but unfortunately, they still don’t cover all scenarios.

While at dashdash we’re still working on bringing you as much flexibility as you may need to have the best experience, here’s how you can achieve this task from our dashboard, today.

Solution

Overview

  1. Convert your data range into a JSON object using our =RANGE2JSON function within a single cell
  2. Parse that JSON using our =PARSE function along with some of the more advanced JsonPath queries (specifically, the Filter Operators) :confetti_ball:
  3. INSERT or OVERWRITE the final/filtered JSON data into a destination table

Explanation

Consider the following setup -

  • Columns A-E (the first 5 columns) are the raw data ranges - highlighted in Yellow
  • The ‘Conditions’ as specified in Column H are highlighted in Blue
  • Table highlighted in Green is where you’d get to see the final result
  • Column K - highlighted in Red - is where all the action lies (i.e. the different steps to follow as proposed along the ‘Overview’ section)

Now, let’s break each of these steps down to understand this a little better!

Create JSON

Converting data range to a JSON object -

K4: =RANGE2JSON(A1:E1,A2:E14)

This would take all the data from A1 to E14 and use the first row (i.e. A1:E1 as headers) along with the rest of the columns to create a key<>value pair that would look something like this -

As the image indicates, the ‘key’ is filled with the header data and ‘value’(s) are data from each row.

Parse JSON

K5: =PARSE(K4,"[?(@."&G4&" == '"&H4&"' && @."&G5&" == '"&H5&"' && @."&G6&" == '"&H6&"')]")

This function is evaluating a key<>value pair. For example, in [?(@.color == 'blue')], the color is the key (for our reference, this would be a “header” value) and blue would be the value (or the data from a specific row).

An extension of this would be to employ multiple such conditions using a logical and (&&) operator, allowing us to filter the JSON :tada:

If we consider @."&G4&" == '"&H4&"' (this snippet is from the first third portion of the function), the G4 cell reference would be the ‘Name’ and cell H4 would represent ‘Jane’.

Write into output table

Writing the data to a destination range -

K6: =OVERWRITE_DATA(K5,G9:K9)

Depending on your need, you are free to use =INSERT_DATA as well :blush: and for any further reference on how to use the JsonPath, visit this link here.

In case you just landed on this article and don’t yet have an account with us, do sign up here to get started!

Cheers :clinking_glasses:
Sourabh | No-Code Builder

2 Likes