Index Match

Hi there,

I need help with creating a formula for using Index Match

What I want to formulate: If California is selected from the State column dropdown, I want it to index match and reference another sheet to pull record # based upon email. (Bar Number CA is the same as Record #)

Current formula:=IF([State Column]@row = "California", IFERROR(INDEX({DS-Record #}, MATCH([WSGR Email]@row, {DS-Email}, 0)), ""), "")

Sheet


Reference Sheet to pull Record #

The only issue here is trying to figure out how to match CA into the formula when on the main sheet it shows California

Tags:

Best Answer

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    Answer ✓

    Hello @Brittanyy

    Apologizes. I was able to get this to work. I created helper columns to assist with the lines that had multiple states.

    The helper columns (CA | UT | NV):

    CA column formula: =IF(OR(CONTAINS("California", State@row), CONTAINS("CA", State@row)), "California", "")

    UT column formula: =IF(OR(CONTAINS("Utah", State@row), CONTAINS("UT", State@row)), "Utah", "")

    NV column formula: =IF(OR(CONTAINS("Nevada", State@row), CONTAINS("NV", State@row)), "Nevada", "")

    _____________

    Bar Number CA formula:

    =IFERROR(IF(CA@row <> "", INDEX(COLLECT({DS-Record #}, {DS-State}, CA@row, {DS-Email}, [WSGR Email]@row), 1)), "")

    Bar Number UT formula:

    =IFERROR(IF(UT@row <> "", INDEX(COLLECT({DS-Record #}, {DS-State}, UT@row, {DS-Email}, [WSGR Email]@row), 1)), "")

    Bar Number NV formula:

    =IFERROR(IF(NV@row <> "", INDEX(COLLECT({DS-Record #}, {DS-State}, NV@row, {DS-Email}, [WSGR Email]@row), 1)), "")

    In these formulas, the ranges came from this sheet:

    And this is what the other sheet looks like:

    *I moved the CA | UT | NV columns to the far right of the sheet. You could also hide them. This appears to be working now.

    Let me know.

    Thanks, Peggy

Answers

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭

    Hello @Brittanyy

    Hope you are well. See if this would work:

    =IF(OR(State@row = "California", State@row = "CA"), IFERROR(INDEX({DS-Record #}, MATCH([WSGR Email]@row, {DS-Email}, 0)), ""), "")

    Let me know.

    Thanks, Peggy

  • Brittanyy
    Brittanyy ✭✭✭✭

    @Peggy Parchert This worked! Thank you so much!

  • Brittanyy
    Brittanyy ✭✭✭✭

    @Peggy Parchert

    One follow-up question if the person was going to choose more than one state, how would that alter the formula

    I would like the bar # for CA and bar # for UT for example. I have a total of 6 separate State columns

    When I plugged in this formula: =IF(OR(State@row = "California", State@row = "CA"), IFERROR(INDEX({DS-Record #}, MATCH([WSGR Email]@row, {DS-Email}, 0)), ""), "")

    It worked when I selected just California, but its blank if I choose more than one state


  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭

    Hello @Brittanyy

    Try this

    =IF(CONTAINS("California", State:State), IF(CONTAINS("CA", State:State), IFERROR(INDEX({DS-Record #}, MATCH([WSGR Email]@row, {DS-Email}, 0)), "")), "")

    Thanks -Peggy

  • Brittanyy
    Brittanyy ✭✭✭✭

    Hi @Peggy Parchert

    When I plugged in the formula it populated in both columns. The second row should only show CA bar # because CA was only selected.


  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭

    @Brittanyy - are you using the identical formula in both columns - [Bar Number CA] and [Bar Number UT]?

    If that's the case, modify the formula in the [Bar Number UT] column as follows:

    =IF(CONTAINS("Utah", State:State), IF(CONTAINS("UT", State:State), IFERROR(INDEX({DS-Record #}, MATCH([WSGR Email]@row, {DS-Email}, 0)), "")), "")

    Hopefully that fixes you up!

  • Brittanyy
    Brittanyy ✭✭✭✭

    @Danielle Arteaga

    When I entered the data with the state 'UT.' The Bar Number for 'CA' is getting duplicated into the Bar Number UT column. Since Utah wasn't selected on that row, it should not populate there

    Below is more information about what I'm attempting to achieve:

    The "State" column is configured as a Multichoice dropdown where individuals completing the form can select as many states as they need. The available options in the dropdown include California, Colorado, Delaware, Illinois, New York, Texas, Utah, Virginia, and Washington.

    My goal is to use an INDEX-MATCH function based on the user's email and the state(s) they selected to retrieve the corresponding "Record #" based on the chosen state(s).

    It's worth noting that some users may select more than one state, each with a different associated "Record #."

    The reference structure is as follows (with the only difference being the formatting of the states):



  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    Answer ✓

    Hello @Brittanyy

    Apologizes. I was able to get this to work. I created helper columns to assist with the lines that had multiple states.

    The helper columns (CA | UT | NV):

    CA column formula: =IF(OR(CONTAINS("California", State@row), CONTAINS("CA", State@row)), "California", "")

    UT column formula: =IF(OR(CONTAINS("Utah", State@row), CONTAINS("UT", State@row)), "Utah", "")

    NV column formula: =IF(OR(CONTAINS("Nevada", State@row), CONTAINS("NV", State@row)), "Nevada", "")

    _____________

    Bar Number CA formula:

    =IFERROR(IF(CA@row <> "", INDEX(COLLECT({DS-Record #}, {DS-State}, CA@row, {DS-Email}, [WSGR Email]@row), 1)), "")

    Bar Number UT formula:

    =IFERROR(IF(UT@row <> "", INDEX(COLLECT({DS-Record #}, {DS-State}, UT@row, {DS-Email}, [WSGR Email]@row), 1)), "")

    Bar Number NV formula:

    =IFERROR(IF(NV@row <> "", INDEX(COLLECT({DS-Record #}, {DS-State}, NV@row, {DS-Email}, [WSGR Email]@row), 1)), "")

    In these formulas, the ranges came from this sheet:

    And this is what the other sheet looks like:

    *I moved the CA | UT | NV columns to the far right of the sheet. You could also hide them. This appears to be working now.

    Let me know.

    Thanks, Peggy

  • Brittanyy
    Brittanyy ✭✭✭✭

    @Peggy Parchert This worked! thank you so much for all your help

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭

    @Brittanyy - Glad it worked. Happy to help

    Peggy

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!