Index Match

Brittanyy

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

Peggy Parchert

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

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

@Peggy Parchert This worked! Thank you so much!

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

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

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

@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

@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

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

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

Peggy Parchert

@Brittanyy - Glad it worked. Happy to help

Peggy