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:
Answers
-
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)
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
-
I received Unparseable for the formula below
=INDEX(COLLECT({Master DPS Directory Range 1}, {Master DPS Directory Range 2}}, First@row, {Master DPS Directory Range 3, Last@row))
-
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)
-
Thank you,
Some of the information came back as invalid value.
Is there a way to do Or Nickname column?
-
Thank you,
Some of the information came back as invalid value.
Is there a way to do Or Nickname column?
-
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), "")
-
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?
-
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), "")
-
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
-
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
-
Yes. It would need to be in both sheets to be able to compare to it for a lookup.
-
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?
-
You would write a second INDEX/COLLECT and nest it in an IFERROR the way I have in my comment from yessterday.
-
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), ""))
-
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)), "")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!