How to VLookup Instructor Client from a sheet

I am trying to do a Vlookup from our DPS Master Directory sheet?

We have the last name and first name in both sheet. I want to pull the Client name for each instructor from the DPS Master Directory sheet.

The formula I used:

=INDEX({DPS Master Directory}, MATCH(Client@row, {DPS Master Directory}, 0))

Below is the sheet where I want the formula to be.

The sheet below is where I want to pull the data from:

Find more posts tagged with

Accepted answers

All comments

AravindGP

Hi @Louis.Smith

The error you're getting is due to usage of the Client field in the destination sheet to populate the client field. Since you would need both the first name and last name combination to get the client name, you can use the below formula.

=INDEX(COLLECT({Client Name column range in source sheet}, {First name column range in source sheet}, First@row, {Last name column range in source sheet}, Last@row)

Louis.Smith

I received Unparseable for the formula below

https://community.smartsheet.com/discussion/comment/384940#Comment_384940

=INDEX(COLLECT({Master DPS Directory Range 1}, {Master DPS Directory Range 2}}, First@row, {Master DPS Directory Range 3, Last@row))

Paul Newcome

You have one too many closing curly brackets after the second range and no closing curly bracket after the third range. There is also a piece missing from the end. Try this:

=INDEX(COLLECT({Master DPS Directory Range 1}, {Master DPS Directory Range 2}, First@row, {Master DPS Directory Range 3}, Last@row), 1)

Louis.Smith

Thank you,

Some of the information came back as invalid value.

Is there a way to do Or Nickname column?

Louis.Smith

https://community.smartsheet.com/discussion/comment/385008#Comment_385008

Thank you,

Some of the information came back as invalid value.

Is there a way to do Or Nickname column?

Paul Newcome

That was expected. I like to make sure the base formula is working before "blanking out" errors so troubleshooting is a little easier if it didn't work the first time.

=IFERROR(INDEX(COLLECT({Master DPS Directory Range 1}, {Master DPS Directory Range 2}, First@row, {Master DPS Directory Range 3}, Last@row), 1), "")

Louis.Smith

https://community.smartsheet.com/discussion/comment/385031#Comment_385031

Thank you, the names that weren't showing in first name or showing in the nickname column of the Master DPS Directory. Is there a way to build into the formula that if the name can't be found in first name then to look in nickname?

Paul Newcome

I didn't see a nickname column in your screenshots, but yes. It is possible. You would write out a second INDEX/COLLECT using the same syntax but matching on the nickname column as a range/criteria set, then use an IFERROR to join the two together.

=IFERROR(IFERROR(INDEX(COLLECT(name), 1), INDEX(COLLECT(nickname), 1), "")

Louis.Smith

Here is the screenshot:

Do I need to add it to the other sheet if I want to compare please see the below:

The blank names like Bos Janna are the columns where nickname is in the first

Louis.Smith

https://community.smartsheet.com/discussion/comment/385072#Comment_385072

Here is the screenshot:

Do I need to add it to the other sheet if I want to compare please see the below:

The blank names like Bos Janna are the columns where nickname is in the first

Paul Newcome

Yes. It would need to be in both sheets to be able to compare to it for a lookup.

Louis.Smith

https://community.smartsheet.com/discussion/comment/385245#Comment_385245

I have nickname in both sheets.

Is there a way to create the same formula we have where if it doesn't find the name in first name that it looks in the nickname column on the DPS Master Directory sheet?

Paul Newcome

You would write a second INDEX/COLLECT and nest it in an IFERROR the way I have in my comment from yessterday.

Louis.Smith

https://community.smartsheet.com/discussion/comment/385352#Comment_385352

Would the whole formula be in one cell?

I tried the below and didn't work:

=IFERROR(INDEX(COLLECT({Master DPS Directory Range 1}, {Master DPS Directory Range 2}, First@row, {Master DPS Directory Range 3}, Last@row), 1), "") = IFERROR(IFERROR(INDEX(COLLECT({Master DPS Directory Range 2}), 1), INDEX(COLLECT({Master DPS Directory Range 4}), 1), ""))

Paul Newcome

Because your syntax is off. You should follow the syntax I had in my earlier comment.

=IFERROR(IFERROR(INDEX(COLLECT(name), 1), INDEX(COLLECT(nickname), 1)), "")

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.