Vlookup in Checkbox Checked Rows in Google Sheets

If you’re using tick boxes to mark rows in Google Sheets, you might come across a situation where you need to perform a VLOOKUP only in the checkbox checked rows. For example, you may want to find the price of only the available (checked) items in a table with columns for item, rate, and availability.

But wait, there’s more! You can take it to the next level.

First, you can find the maximum or minimum rate of the items in the ticked rows. Then, you can use that value to look up the corresponding item.

Luckily, performing this task in Google Sheets is quite simple because we have the Filter function, which allows us to extract only the checked rows from the table.

In this article, I’ll show you how to Vlookup only in checkbox-checked rows in Google Sheets. I’ll also walk you through the process of finding the maximum or minimum rate at the end.

Examples to Vlookup in Checkbox Checked Rows in a Table

Using Filter within Vlookup

Let’s say you have a table with three columns: items, rates, and availability. Your task is to search for specific items and return their rates only if they are available.

Here’s the table:

You can use the VLOOKUP formula with the following parameters:

• search_key (the items to search): F3:F5
• range: B2:D17 (filtered by the following FILTER formula to exclude non-available items: filter(B3:C17,D3:D17))
• index: 2 (the rate column number from the left of the range)
• is_sorted: FALSE

Here’s the Vlookup formula for checkbox checked rows:

=vlookup(F3,filter(B3:C17,D3:D17),2,0)

Insert this formula in cell G3 and copy-paste it down for other items (F4 and F5).

Alternatively, if you want to use all the search keys at once, you can use the ArrayFormula:

=ArrayFormula(vlookup(F3:F5,filter(B3:C17,D3:D17),2,0))

Using Logical Test

Instead of using Filter, you can replace it with an IF in the above formula.

The range to use will be:

if(D3:D17,B3:C17)

So, the Vlookup formula for checkbox checked rows will be:

=ArrayFormula(ifna(vlookup(F3:F5,if(D3:D17,B3:C17),2,0)))

In this case as well, I’ve used multiple search keys at once, so the ArrayFormula is necessary. Even if you use F3 instead of F3:F5, you still need to use the ArrayFormula function because the IF logical test requires it.

Vlookup Max or Min in Checkbox Checked Rows in Google Sheets

Now, let’s explore a different scenario.

In this case, you want to find the maximum or minimum value in the checked rows and then use it as the search key in a Vlookup. This will help you retrieve the max rate of available items in a table.

To find the maximum value excluding checked rows, you can use the MAXIFS formula:

=maxifs(C3:C17,D3:D17,true)

The above formula will serve as the search key.

Previously, we used the following filter as the range for Vlookup in the checkbox checked rows:

filter(B3:C17,D3:D17)

But it won’t work here. Can you guess why?

The above formula returns items in the first column and rates in the second column. However, we want the reverse because we want to look up the max value (rate). So the range will be as follows:

filter({C3:C17,B3:B17},D3:D17)

With the search key and the updated range, coding the formula for Vlookup Max in Checkbox Checked Rows in Google Sheets becomes easier:

=vlookup(maxifs(C3:C17,D3:D17,true),filter({C3:C17,B3:B17},D3:D17),2,0)

And if you want to find the minimum, simply replace MAXIFS with MINIFS.

That’s all there is to it! You can now confidently perform Vlookup in checkbox checked rows and even find the maximum or minimum values. Happy Sheets-ing!

Find more helpful tips and tricks for Google Sheets at Crawlan.com