IF Formula with Nested VLookup

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

Find more posts tagged with

Accepted answers

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.

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.