Compare data to another sheet and if it matches, run VLOOKUP

This discussion was created from comments split from: IF and VLOOKUP.

Answers

  • MKellermann
    edited 01/24/22

    Thank you for this but I am getting a response invalid operation.

    In my process I am trying to compare the data to another sheet and if it matches then run the VLOOKUP

    Used IF with VLOOKUP and got "#INVALID OPERATION"

    =IF([Client Name]@row = {Client Name Master}, VLOOKUP([Client Name]@row, {Master Table}, 2, 0), "N/A")

    Also tried INDEX/MATCH and I keep getting the same error (Example above

    =INDEX({Master Table}, MATCH([Client Name]@row, {Master Table}, 0))

    I appreciate the help on this matter

  • Hi @MKellermann

    If there isn't a match for your [Client Name]@row, then an INDEX(MATCH formula will give you a NO MATCH error. This means that we can use an IFERROR Function around your formula to output "N/A", instead of an IF statement.

    An INDEX(MATCH formula requires two separate {cross sheet references}. Each reference will be one unique column (versus a range of columns).

    Try something like this:

    =IFERROR(INDEX({Column with Data to Return}, MATCH([Client Name]@row, {Client Name Column}, 0)), "N/A")

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!