# Help needed with VLOOKUP or IF Formula

Hello,

I am in need of a formula to reference another sheet and bring back either 1 value if there's only one for that cell or show something as "varies" in case there are more results for that value. For example, I have resource John Smith working on 2 different projects, in this case I want the formula to just say multiple and not bring back the actual project. While for Jane Doe who only has one project, I want the formula to bring the current project on this other sheet. Any help is appreciated! Thanks in advance

• ✭✭✭✭✭✭

Try something like this...

=IF(COUNTIFS({Other Sheet Resource Column}, "John Smith") = 1, INDEX({Other Sheet Current Project Column}, MATCH("John Smith", {Other Sheet Resource Column}, 0)), "Varies")

If there is only 1 "John Smith" then use the INDEX/MATCH to pull the project, otherwise output "Varies".

• ✭✭✭✭✭✭

There are a couple of misplaced parenthesis.

=IF(COUNTIFS({Resource: Full Name}, "John Smith", {End Date}, DATE(2021, 3, 5)) = 1, INDEX(COLLECT({CE Range 1}, {Resource: Full Name}, "John Smith", {End Date}, DATE(2021, 3, 5)), 1), "Varies")

• ✭✭✭✭✭✭

Try something like this...

=IF(COUNTIFS({Other Sheet Resource Column}, "John Smith") = 1, INDEX({Other Sheet Current Project Column}, MATCH("John Smith", {Other Sheet Resource Column}, 0)), "Varies")

If there is only 1 "John Smith" then use the INDEX/MATCH to pull the project, otherwise output "Varies".

• Hi Paul,

Thank you for the response, I wrote the formula as you suggested but no results show up. I have more than 10 rows with the same resource name and all the projects that they're listed on but it doesn't seem to be working on my side.

• ✭✭✭✭✭✭

Are you able to show a screenshot of the formula as well as a screenshot of the data? Regardless of the source data, the formula should not be outputting a blank.

Formula is =IF(COUNTIFS({Resource: Full Name}, "John Smith") = 1, INDEX({CE Range 1}, MATCH("John Smith", {Resource: Full Name}, 0), "Varies"))

• ✭✭✭✭✭✭

You have a misplaced parenthesis. Tyr this one...

=IF(COUNTIFS({Resource: Full Name}, "John Smith") = 1, INDEX({CE Range 1}, MATCH("John Smith", {Resource: Full Name}, 0)), "Varies")

• THANK YOU SO MUCH!!!!!

• On that note, do you think I can add another criteria to the formula? To look at a projects within a particular date?

• ✭✭✭✭✭✭

Yes. You would add the range/criteria to the COUNTIFS, and then you would change the INDEX/MATCH to an INDEX/COLLECT.

=IF(COUNTIFS({Resource: Full Name}, "John Smith", {Date Range}, date_range_criteria) = 1, INDEX(COLLECT({CE Range 1}, {Resource: Full Name}, "John Smith", {Date Range}, date_range_criteria), 1), "Varies")

The date range criteria is going to vary depending on your exact needs.

• Hi Paul,

I tried this formula but I have an exact date I want the formula to look at and it wasn't working. Perhaps it might need more tweaking?

• ✭✭✭✭✭✭

For an exact date, you will want to use a DATE function in the "date_range_criteria" portion of the above.

DATE(yyyy, mm, dd)

• edited 04/05/21

I tried that but it's showing Unparseable

=IF(COUNTIFS({Resource: Full Name}, "John Smith", {End Date}, DATE(2021, 3, 5) = 1, INDEX(COLLECT({CE Range 1}, {Resource: Full Name}, "John Smith", {End Date}, DATE(2021, 3, 5), 1), "Varies")))

• ✭✭✭✭✭✭

There are a couple of misplaced parenthesis.

=IF(COUNTIFS({Resource: Full Name}, "John Smith", {End Date}, DATE(2021, 3, 5)) = 1, INDEX(COLLECT({CE Range 1}, {Resource: Full Name}, "John Smith", {End Date}, DATE(2021, 3, 5)), 1), "Varies")

• Thank you so much! Appreciate all your help with my questions!

• ✭✭✭✭✭✭

Happy to help. 👍️

• Overachievers Alumni

Paul, I tried to find you on Linkedin to no avail. You really know your stuff! I'm going to dig through your comments on Contact List Index/Match in a bit and see if I can get it going.

Thanks for contributing so much to the Community!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!