VLOOKUP using flag or checkbox

Aprylli Bernarte

How do I use VLOOKUP with Flag as the search value?

I tried the following:

=VLOOKUP("Checked", {Range 2}, 3, 0)

=VLOOKUP("checked", {Range 2}, 3, 0)

=VLOOKUP("true", {Range 2}, 3, 0)

=VLOOKUP(true, {Range 2}, 3, 0)

=VLOOKUP(1, {Range 2}, 3, 0)

All of the returning #NO MATCH even if there is a row that has a Flag checked.

Here's a screenshot of the lookup table:

Paul Newcome

It looks like the VLOOKUP (and MATCH) don't register on those types of columns. I would suggest creating a hidden helper column with a basic IF formula that will output some text on the row that is flagged then reference that in your VLOOKUP.

Genevieve P.

Hi @Aprylli Bernarte

I think @Paul Newcome's suggestion of a helper column is a better way to go... the flag value can be indicated by the number 1 in a formula, however within a VLOOKUP this isn't a unique value. This means that if you have more than 1 row with the flag it won't be able to recognize which row's data you want to bring across. Does that make sense?

Can you describe your process a bit more? I wonder if a Report that's filtered on the Flag might be a better way to achieve what you're looking for.

Paul Newcome

@Genevieve P I did some testing. You can't use 1 as the value to search for, but true worked with a checkbox and a flag type column.

=VLOOKUP(true, {range}, 3, false)

It acts the same way it would if there was not unique row data in that it pulls from the first row that is true.

@Aprylli Bernarte Double check that your range is set correctly. I have noticed that sometimes when I go to create a cross sheet reference, it doesn't load in the popup window very quickly. If I select the range before it loads, when it does load it resets my range selection to the top left cell. It may be that your range just needs adjusted as I was able to get it to work using a search value of true.

Genevieve P.

Ah, good catch Paul!! Thank you.

Aprylli Bernarte

Thank you Paul and Genevieve. When I tried again using true as the search_value, the formula worked!

Paul Newcome

Happy to help. 👍️

I didn't think this was initially the issue because your original post included a formula that used true as the search value. Glad we were able to get it working for you.