IF Formula with Nested VLookup

Options

I have employees take a test. They are allowed more than one attempt. The results of each test are recorded on a Smartsheet tracker. I then have a second tracker with each employee name, where I want to pull the result for each of their attempts. I'm getting an INVALID OPERATION error.

Here's what I want:

If on the Results tracker, in the "Attempt" column, it displays "1st Attempt" then I want it to return the "Attempt Status" to my Clinical Skills Tracker by looking up the Users Initials/ID in both sheets.

Here is my formula: =IF({Attempt} = "1st Attempt", VLOOKUP([User Initials]@row, {Skills Measure Results}, 3))



Best Answer

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Sam H. do you have any initials in "[User Initials]@row"? If not, you would probably get an error. Can you show what that column looks like? Also double check to makes sure you've properly selected your columns in your cross-sheet references.

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Sam H.

    A different approach is needed to achieve your desired outcome.

    =INDEX(COLLECT({Skills Measure Results Attempt Status column}, {Skills Measure Results UserID column}, UserID@row, {Skills Measure Results Attempt column}, "1st Attempt"),1)

    You will need to build each of the references above. Unlike a vlookup which generates a table of columns, the Index/Match or Index/Collect typically uses individual columns. The Index/Collect also allows multiple criteria to be used in the formula. The Index/Match or Index/Collect allows better sheet performance than the vlookup.

    Will the above work for you?

    Kelly

  • Sam H.
    Sam H. ✭✭✭✭
    Options

    Thanks Kelly. That makes sense, but when I enter that in, I get an Invalid Value error.



  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Sam H. do you have any initials in "[User Initials]@row"? If not, you would probably get an error. Can you show what that column looks like? Also double check to makes sure you've properly selected your columns in your cross-sheet references.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!