Help! This Vlookup is Driving Me Crazy.

Tiffany Covin-Jones

Hi,

I'm loosing mind with

CopyReady.png

MC MOB Ready for Web Ops.PNG

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.

Ramzi K

Try removing the "= true" from your VLOOKUP.

I hope that works.

Cheers,

Ramzi

www.cedartreeconsulting.com

Tiffany Covin-Jones

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

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?

Tiffany Covin-Jones

@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

@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.