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!
Best Answer
-
Hi
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
Answers
-
Hi
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
-
@KPH This worked, thank you!
-
Wonderful! Thanks for letting me know.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.4K Get Help
- 432 Global Discussions
- 152 Industry Talk
- 493 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 76 Community Job Board
- 504 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!