IF Formula with Nested VLookup

Samantha S.

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))

Lucas Rayala

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.

Kelly Moore

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

Samantha S.

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

Lucas Rayala

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.