INDEX, MATCH, IF Formula

Options

I am trying to pull a value from another sheet if the cell matches another cell and IF it is not checked off in the other sheet:

If the ABV on sheet 1 matches the course in sheet 2, I'd like to pull the "EPM" from sheet 2 into sheet 1, but ONLY if the "Archived" column on sheet 2 is not checked.... (I want EPM on sheet 1 to pull "Shelly")

Sheet 1:

Sheet 2:

Thank you!

Tags:

Answers

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    @brianne111081

    Hi Brianne,

    See if this one works for you...

    =IF(IFERROR(INDEX({Archived}, MATCH([ABV.]@row, {Course}, 0)), "Course Not Found") = "Course Not Found", "Course Not Found", IF(INDEX({Archived}, MATCH([ABV.]@row, {Course}, 0)) = 0, IF(INDEX({EPM}, MATCH([ABV.]@row, {Course}, 0)) = "", "No EPM", INDEX({EPM}, MATCH([ABV.]@row, {Course}, 0))), "Course Archived"))

    I built it so it would tell you what's going on "No EPM", "Course Not Found", "Course Archived".

    Sheet1:



    Sheet2:


    Please let me know if that works like you want it to!

    -Ryan

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Maximus
    Maximus ✭✭
    Options

    @brianne111081 ,

    You can find more information in the article by @Brett Wyrick.

    Alternatively, if you are searching for values from Sheet 1 in Sheet 2 across the entire "Course" column, you can use VLOOKUP function instead of INDEX and MATCH combination:

    =IF(ISERROR(VLOOKUP([ABV.]@row, {Sheet 2 Range 1}, 4, false)=FALSE,IF(VLOOKUP([ABV.]@row, {Sheet 2 Range 1}, n, false)=true, VLOOKUP([ABV.]@row, {Sheet 2 Range 1}, m, false),""),"").

    Here {Sheet 2 Range 1} represents your [Range to look for Identifier], and "m" and "n" are your "EPM" and "Archived" column order numbers with respect to the "Course" field.

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    @Maximus Great point! One caution with VLOOKUP is that if the column sequence changes (someone adds an additional column in the middle), the formula breaks. I was a VLOOKUP fan forever then, my columns were moved and all of my formulas were hosed. I then became an INDEX/MATCH guy and haven't looked back. :)

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!