VLOOKUP using flag or checkbox

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
    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.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin

    Ah, good catch Paul!! Thank you.

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

  • Paul Newcome
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!