VLookup with 2 different criteria

Rsparks
Rsparks ✭✭
edited 09/22/23 in Formulas and Functions

Hi everyone,

I am trying to figure out if there is an OR function that would work, or another way to look for only 1 of 2 values for a Vlookup. I have a cute little formula that helps me know if people signed off on a project - (the completion page looks up on the form results for an email and a date, and reports back to with a Vlookup formula - and when the cell is no longer blank, it turns my indicator green.) This works great for everyone but one stinker, who has 2 emails and alternates between them.

This is my original formula

=IFERROR(VLOOKUP([Email at _____]@row, {Training Sheet Range 1}, 2, false), " ")

I tried a few variations, but I keep getting errors. Any suggestions?

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭

    Hello @Rsparks

    I see you're using an IFERROR(), if this is because you are getting a #NO MATCH in this case you can add a second INDEX(MATCH()) as your 'value if false' that is formulated in a way to get the other value. This is what I have done for situations where I am trying to pull in 2 options. The first INDEX(MATCH()) looks for the first option and if it errors out the second kicks in.

    👨🏼‍💻 Dan Palenchar | School of Sheets Solutions Consulting (Smartsheet Aligned Gold Partner)

    If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and/or ❤️Awesome).

    🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

    ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!