Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

  • Community Champion
    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

  • Community Champion
    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

  • @KPH This worked, thank you!

  • Community Champion

    Wonderful! Thanks for letting me know.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions