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

  • Ramzi K
    Ramzi K ✭✭✭✭✭

    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.

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    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


  • Tiffany Covin-Jones
    edited 09/23/20

    @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!