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:

Screen Shot 2021-02-23 at 1.27.17 PM.png

Find more posts tagged with

Formulas

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.