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:
Answers
-
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.
-
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.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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.
-
Ah, good catch Paul!! Thank you.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thank you Paul and Genevieve. When I tried again using true as the search_value, the formula worked!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!