INDEX MATCH Formula

Hi, I used this formula in one column on Sheet 1, it looks at Sheet 2 and finds the Site Name and if the Status is Committed, it pulls in the number of Committed Trees. It works just fine for the Committed Trees column.

=IF(INDEX({Inventory Agreements - Status}, MATCH([Site Name]@row, {Inventory - Site Name}, 0)) = "Committed", INDEX({Inventory Agreements - # Trees}, MATCH([Site Name]@row, {Inventory - Site Name}, 0)), "")

I would also like to use this formula in another column on Sheet 1, the Potential Trees column, the only think that is different is the status it's looking for on Sheet 2 is Pending instead of Committed. Nothing is being returned when I change the Status in the formula. Any suggestions why it's not working on the Potential Trees column?

=IF(INDEX({Inventory Agreements - Status}, MATCH([Site Name]@row, {Inventory - Site Name}, 0)) = "Pending", INDEX({Inventory Agreements - # Trees}, MATCH([Site Name]@row, {Inventory - Site Name}, 0)), "")

Thanks!

Find more posts tagged with

Formulas

IF(

INDEX

MATCH

Accepted answers

KPH

I think you might have the same site name appear more than once in your list, and the committed ones are at the top.

Because of the IF element in your formula, if the first match of site name is not equal to the value in your IF (in the second example, if the site name is not Pending) then the IF will resolve as false and the "" will be returned.

Rather than combining and IF with the INDEX and MATCH, how about using INDEX COLLECT to evaluate both conditions at once. INDEX COLLECT will let you return the Number of trees if the Site Name matches and the Inventory status matches. No need for the IF.

Try something like this:

=INDEX(COLLECT({Inventory Agreements - # Trees}, {Inventory - Site Name}, [Site Name]@row, {Inventory Agreements - Status}, "Pending"), 1)

It will also give you more flexibility should you want to add additional criteria as you can keep extending the COLLECT:

=INDEX(COLLECT({Inventory Agreements - # Trees}, {Inventory - Site Name}, [Site Name]@row, {Inventory Agreements - Status}, "Pending", {Has bears},"Yes"), 1)

Hope this helps

All comments

KPH

I think you might have the same site name appear more than once in your list, and the committed ones are at the top.

Try something like this:

=INDEX(COLLECT({Inventory Agreements - # Trees}, {Inventory - Site Name}, [Site Name]@row, {Inventory Agreements - Status}, "Pending"), 1)

It will also give you more flexibility should you want to add additional criteria as you can keep extending the COLLECT:

=INDEX(COLLECT({Inventory Agreements - # Trees}, {Inventory - Site Name}, [Site Name]@row, {Inventory Agreements - Status}, "Pending", {Has bears},"Yes"), 1)

Hope this helps

alys

@KPH This worked, thank you!

KPH

Wonderful! Thanks for letting me know.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.