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
Best 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
-
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
-
@Peggy Parchert This worked! Thank you so much!
-
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
-
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
-
When I plugged in the formula it populated in both columns. The second row should only show CA bar # because CA was only selected.
-
@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!
-
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):
-
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 This worked! thank you so much for all your help
-
@Brittanyy - Glad it worked. Happy to help
Peggy
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!