Help needed with VLOOKUP or IF Formula

Jona Gjylameti

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

Paul Newcome

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".

Paul Newcome

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

Paul Newcome

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".

Jona Gjylameti

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.

Paul Newcome

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.

Jona Gjylameti

Please see below

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

Paul Newcome

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

Jona Gjylameti

THANK YOU SO MUCH!!!!!

Jona Gjylameti

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

Paul Newcome

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.

Jona Gjylameti

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?

Paul Newcome

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)

Jona Gjylameti

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

Paul Newcome

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

Jona Gjylameti

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

Paul Newcome

Happy to help. 👍️

Andria Hoyle

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!