I need a formula to return information with multiple criteria.

Options

I have looked through a TON and can not figure this one out. The request types determine what level needs to handle it (more testing column), if it is the owner of the account or senior level of the account. The customer details on a different sheet that hold the information on who the senior and owner is per the customer. I'm trying to have it pull the information using the customer number and knowing which column to pull the name from, ie either the owner or senior.

i just can't seem to get any of them to work.

any help on this would be greatly appreciated.

customer number, either owner/senior columns to return person's name

Best Answer

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    Answer ✓
    Options

    Oh perfect, then this should be very straightforward.

    In the column where you want the Owner or Senior contact to appear, use this formula. Set it as a column formula (right click, Convert to Column Formula) so that it runs for all rows regardless of changes to the rows.

    = IF([Owner/Senior Level]@row="Owner", INDEX({Owner}, MATCH([Customer Number]@row, {Customer Number}, 0)), INDEX({Senior}, MATCH([Customer Number]@row, {Customer Number}, 0)))

    This formula assumes that it's either Owner or Senior.

    The {} bracketed references are cross-sheet references that point to your detailed customer info sheet. You create them when you're typing out this formula and click "Reference Cells from Another Sheet" in the popup formula helper box. Then browse to the detailed sheet and select the appropriate column.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    Options

    Hi Amber - what Request Types determine whether you use Senior or Owner? Any other criteria other than Request Type?

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • AmberEspindola
    Options

    The type of request determines if it is senior or owner but i have a formula that does that and puts it in a column itself.

    So in this case, a cash move is what an owner would work.

    this is the details for the information i need returned, so it would look at the customer number and then from the owner/senior level know which column to return with the name.

    i can do it and excel but its not the same here

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    Answer ✓
    Options

    Oh perfect, then this should be very straightforward.

    In the column where you want the Owner or Senior contact to appear, use this formula. Set it as a column formula (right click, Convert to Column Formula) so that it runs for all rows regardless of changes to the rows.

    = IF([Owner/Senior Level]@row="Owner", INDEX({Owner}, MATCH([Customer Number]@row, {Customer Number}, 0)), INDEX({Senior}, MATCH([Customer Number]@row, {Customer Number}, 0)))

    This formula assumes that it's either Owner or Senior.

    The {} bracketed references are cross-sheet references that point to your detailed customer info sheet. You create them when you're typing out this formula and click "Reference Cells from Another Sheet" in the popup formula helper box. Then browse to the detailed sheet and select the appropriate column.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • AmberEspindola
    edited 06/20/24
    Options

    Thank you!!! That is exactly what i needed!!!

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    Options

    Hooray!

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!