Help! This Vlookup is Driving Me Crazy.
Hi,
I'm loosing mind with
this vlookup and don't know if I should use an index/match. However, I have one sheet whereas I'm trying to prefill the 'Copy Ready' cells with yes or no based on if the value is found in another sheet and if in that same row where the value is found it's checked off as true under a column named "Web Ops ready". Attached are images of my spreadsheets and I was able to get it to work when the value is found; however,when the value isn't found it say "#NO MATCH." So I tried to wrap my vlookup in an IFERROR and now it says no for the matches. PLEASE HELP. I'm loosing my mind over this one. Thanks in advance.
Answers
-
Try removing the "= true" from your VLOOKUP.
I hope that works.
Cheers,
Ramzi
www.cedartreeconsulting.com
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
-
Here's my initial vlookup
=IF(VLOOKUP([Master Style]1, {MC MOB Ready for Web Ops}, 11, false) = true, "yes", "no")
You're recommending this:
=IF(VLOOKUP([Master Style]1, {MC MOB Ready for Web Ops}, 11, false), "yes", "no")
When I remove ="true" there's no change to the no match; and for reference I added ="true" because the check mark lives in column 11 on the outside sheet, so my thought in my initial formula was to say if the value is found and column 11 is checked say yes otherwise say no. However, it just says #NO MATCH. I would like to replace the #NO MATCH with no.
-
I think this one will be a simple fix. You're using IF but I think you're looking for IFERROR. It will either return he result of your first statement, or some other message if there is an error. I think in your case the formula would look like this:
=IFERROR(VLOOKUP([Master Style]1, {MC MOB Ready for Web Ops}, 11, false), "no")
Does that work for what you're looking for?
-
@David Tutwiler Thanks for the suggestion, but that doesn't work either. I get a invalid column value error. See screen shot below. Column 11 in the reference sheet is a checkbox is that reason for the error. I feel like I'm so close to solving this and when I used this formula (=IF(VLOOKUP([Master Style]1, {MC MOB Ready for Web Ops}, 11, false) = true, "yes", "no") it worked, but I needed to account for the No Matches to say "no" instead of #NO MATCH
-
@Paul Newcome you're always a great solver of these formulas - especially those related to vlookup and index/match from what I've found. Any suggestions or recommendations? Would greatly appreciate it.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!