lookup formuale with condition

Gaurav Chauhan
Gaurav Chauhan ✭✭✭✭✭✭
edited 01/19/23 in Formulas and Functions

Hi,

I need help with the lookup formulae. Basically, I am trying to populate the Metric1 and 2 from Shee1 onto Sheet2 considering that Metric values sit in the row of A1 and B1 & ...

Can you please help.


Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Gaurav Chauhan

    When doing a lookup with multiple criteria, the combination of INDEX/COLLECT are the functions to use. A COLLECT has the syntax (COLLECT(information you want to collect, range 1, criteria 1, range 2, criteria 2, etc). The number 1 at the end of the formulas are the row index required in the INDEX function.

    Metric #1

    =INDEX(COLLECT({Sheet 1 Metric 1 column}, {Sheet 1 Project column}, Project@row, {Sheet 1 Period column}, Period@row),1)


    Metric #2

    =INDEX(COLLECT({Sheet 1 Metric 2 column}, {Sheet 1 Project column}, Project@row, {Sheet 1 Period column}, Period@row),1)

    Because the ranges are cross sheet references you will need to manually insert them through the Formula window - you cannot simply copy paste the formula into your sheet. Your ranges will have different names (you can rename them at will). I wanted you to be clear on what the ranges referred to.

    Let me know if you have any questions

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!