How to VLookup Instructor Client from a sheet

Louis.Smith

https://community.smartsheet.com/discussion/comment/385365#Comment_385365

=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(name), 1), INDEX(COLLECT(nickname), 1)), "")

Using the above in the cell it is saying that the syntax isn't quite correct

Louis.Smith

https://community.smartsheet.com/discussion/comment/385365#Comment_385365

When adding the formula suggested below:

=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(name), 1), INDEX(COLLECT(nickname), 1)), "")

It is saying the sytax isn't quite right

Paul Newcome

Because the syntax is still not correct. You need to make sure you are using the same syntax (aside from the internal piece of each COLLECT function) to include = signs, parenthesis, and other functions.

I am indicating:

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

You are doing:

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

Not only is the overall syntax incorrect, but you are not filling out the COLLECT portion of the INDEX/COLLECT for the nickname.

Louis.Smith

https://community.smartsheet.com/discussion/comment/385375#Comment_385375

I tried:

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

Am I missing something or adding information on the formula that is not needed?

Paul Newcome

You are missing the rest of the second COLLECT function.

Louis.Smith

https://community.smartsheet.com/discussion/comment/385384#Comment_385384

I am not sure what should be added for the rest of the second collect function.

Paul Newcome

It is going to be exactly the same as the first COLLECT except instead of matching on first and last you are match on the nickname.

Louis.Smith

https://community.smartsheet.com/discussion/comment/385388#Comment_385388

I tried:

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

Paul Newcome

The syntax is still off in the second COLLECT. Make sure that the curly brackets and commas are all how they should be.

Louis.Smith

https://community.smartsheet.com/discussion/comment/385397#Comment_385397

I tried the below:

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

The second collect is exactly the same as the above I am getting an error code as well

Paul Newcome

Now you have adjusted the closing parenthesis. Make sure that parenthesis match what I suggested.

Louis.Smith

https://community.smartsheet.com/discussion/comment/385404#Comment_385404

I tried the below:

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

I feel as if I matched the parenthesis.

Paul Newcome

Your closing parenthesis are still off. There should only be one after the first 1 and two after the second 1.