If you use the Tick boxes to mark rows in Google Sheets, at some point in time, you may require to VLOOKUP only in checkbox checked rows.

For example, we can use this to find/lookup the price of only the available (checked) items in a table formatted as; item (text field), rate (numeric field), and availability (logical field, i.e., tick boxes).

What more! We can take it to the next level.

First, find the max or min rate of items in the ticked rows. Then use that value to look up the corresponding item.

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

In this post, you can learn how to Vlookup only in checkbox-checked rows in Google Sheets. I have also included the Max/Min part at the end.

Below you can find the sample data and a couple of examples.

Examples to Vlookup in Checkbox Checked Rows in a Table

Using Filter within Vlookup

I have a three-column table with items, rates, and availability in the first, second, and third columns.

How to search for a few items and return their rate only if they are available? That’s the problem to solve.

Syntax: VLOOKUP(search_key, range, index, [is_sorted])

search_key (the items to search): F3:F5.

range: B2:D17 – Here, we want the checked rows. So we will use the following FILTER formula as the range that filters out non-available items (unchecked rows).

filter(B3:C17,D3:D17)

index: 2 (rate column number from the left of the range)

is_sorted: FALSE

So here is the Vlookup in checkbox checked rows formula.

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

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

Alternatively, you can use all the search_keys in one go! For that, you may additionally require to use the ArrayFromula.

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

Using Logical Test

We can replace Filter with an IF in the above formula.

Here is the range to use.

if(D3:D17,B3:C17)

So the Vlookup in check boxes checked rows is as follows.

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

Here also, I’ve used multiple search keys in one go. So, the ArrayFormula is a must.

Even if you use F3 instead of F3:F5, you must use the ArrayFormula function because the IF logical test needs it.

Vlookup Max or Min in Checkbox Checked Rows in Google Sheets

Here is an entirely different scenario.

Here we will find the max or min in the checked rows and then use it as the search key in a Vlookup.

It will help us to return max rate of available items in a table.

With the help of the following MAXIFS, we can get the max value excluding checked rows.

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

The above is the search key.

Earlier we used the following filter as the range for Vlookup in the checkbox checked Rows. It won’t work here.

filter(B3:C17,D3:D17)

Do you know why?

The above formula returns items in the first column and rates in the second column.

We want the reverse because we want to look up the max value (rate). So it must be in the first column in the lookup table. So the range will be as follows.

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

We have the search key and range. Now it’s easy to code the formula to Vlookup Max in Checkbox Checked Rows in Google Sheets.

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

What about Min?

Replace Maxifs with MINIFS. That’s all!

Thanks for the stay. Enjoy!