How do i use the INDEX/MATCH formula to populate a cell based on a dropdown selection?
I have two sheets, one has my data that has the dropdown fields and the field I want to autopopulate:
The other sheet has the data that I want to use to autopopulate the Initiative cell:
I believe it is an INDEX/MATCH function, but I cannot seem to get it right.
Best Answers

It worked!!! Thank you. I did what you said and made sure all of the words were the same on both sheets and tested it using the word apple and it pulled in the correct information!! Thank you so much.

Phew! Glad it worked. For sustainability in the future, you might consider easing the restrictions and only look for the Goal number. I wanted to make sure that was the problem before suggesting we loosen the criteria. The other formula is working so you don't have to change to the below you would just have to make sure you always have a word for word match in both sheets.
The formula below would only look for the Goal # (the first 6 characters from the Left)
=INDEX(COLLECT({source sheet Initiative}, {source sheet Initiative Year}, [FY Year]@row, {source sheet Goal}, CONTAINS(LEFT(Goal@row,6),@cell)), 1)
cheers
Kelly

Hey Shawn
Yes, we can mask the error messages. Because this is a lookup type formula, you might consider either restricting the dropdown, or, send an automated alert when a match is not found. Either will help you maintain cleaner sheets.
I wasn't sure which formula you decided to use so I added the IFERROR to both.
The syntax for the IFERROR is
=IFERROR(whatever your entire formula is, whatever you want to happen if an error is found)
=IFERROR(INDEX(COLLECT({source sheet Initiative}, {source sheet Initiative Year}, [FY Year]@row, {source sheet Goal}, Goal@row), 1),"")
=IFERROR(INDEX(COLLECT({source sheet Initiative}, {source sheet Initiative Year}, [FY Year]@row, {source sheet Goal}, CONTAINS(LEFT(Goal@row,6),@cell)), 1),"")
In the formula above, if errors are found, the cell will remain blank. If you want specific text inserted, the double quotes would be replaced with the desired text enclosed in quotes. "the text you want"
Do either of the formulas above work for you?
Kelly
Answers

Hey @Shawn Burnett
Try this
=INDEX(COLLECT({source sheet Initiative}, {source sheet Initiative Year}, [FY Year]@row, {source sheet Goal}, Goal@row), 1)
Remember you'll need to build the cross sheet references individually rather than just copingpasting the above formula into your sheet.
Let me know if this works for you
Kelly

I entered this: =INDEX(COLLECT({Initiative Data Range 1}, {Initiative Data Range 2}, [FY Year]@row, {Initiative Data Range 3}, Goal@row), 1) and I got "INVALID VALUE"

Hey Shawn
I tested the formula on my sheet and it worked. I could replicate an error when I didn't have matching data in the other sheet. Before we hide the error with IFERROR, let's verify the cause.
Here are some things to check
Double check your ranges  each one should be a single column. One good practice is to rename the generically named smartsheet rangenumber cross sheet references to reflect the actual name of the columns used. This will help both you and the community better understand your formulas.
The cell where you entered this formula  does this row have corresponding data to pull in from your other sheet?
As a test, delete the goal range and criteria from the formula to see if the formula works with less filters. Put those back then delete the year range and criteria.
Kelly

Hi Kelly,
This is what I entered: =INDEX(MATCH({Source Sheet Initiative}, {Source Sheet Initiative Year}, [FY Year]@row, {Source Sheet Goal}, Goal@row), 1) and now i get an Incorrect Argument.
I renamed my ranges as "source sheet" for the sheet I'm trying to pull the information from. I can't see where my formula is going wrong.

Sorry, the formula begins with =INDEX(COLLECT

Hey Shawn
I could replicate an error when I didn't have matching data in the other sheet. Have you tried dragging the formula down a few rows to see if the problem is caused by no data on the row you're sitting in now? For example, I see that Goal #1 is written differently between the two sheets. This would cause no data to be pulled and would give the error. The word 'years' is abbreviated on one sheet but not the other. I see differences in Goal #2 as well. I believe the inconsistency between the two sheets in the exact wording and capitalization is the cause of your error.
Try typing the word 'Apple' into the Goal of both sheets. Copy Paste the Year from one sheet to another to ensure an exact match. See if the formula works then.

It worked!!! Thank you. I did what you said and made sure all of the words were the same on both sheets and tested it using the word apple and it pulled in the correct information!! Thank you so much.

Phew! Glad it worked. For sustainability in the future, you might consider easing the restrictions and only look for the Goal number. I wanted to make sure that was the problem before suggesting we loosen the criteria. The other formula is working so you don't have to change to the below you would just have to make sure you always have a word for word match in both sheets.
The formula below would only look for the Goal # (the first 6 characters from the Left)
=INDEX(COLLECT({source sheet Initiative}, {source sheet Initiative Year}, [FY Year]@row, {source sheet Goal}, CONTAINS(LEFT(Goal@row,6),@cell)), 1)
cheers
Kelly

The formula works great, but i have one small problem: because the Goal column is a dropdown and entires are not restricted to using only the dropdown responses, when something is entered that is not from the dropdown, the Inititative column shows #INVALID VALUE. How would I fix that formula to not show invalid value?

Hey Shawn
Yes, we can mask the error messages. Because this is a lookup type formula, you might consider either restricting the dropdown, or, send an automated alert when a match is not found. Either will help you maintain cleaner sheets.
I wasn't sure which formula you decided to use so I added the IFERROR to both.
The syntax for the IFERROR is
=IFERROR(whatever your entire formula is, whatever you want to happen if an error is found)
=IFERROR(INDEX(COLLECT({source sheet Initiative}, {source sheet Initiative Year}, [FY Year]@row, {source sheet Goal}, Goal@row), 1),"")
=IFERROR(INDEX(COLLECT({source sheet Initiative}, {source sheet Initiative Year}, [FY Year]@row, {source sheet Goal}, CONTAINS(LEFT(Goal@row,6),@cell)), 1),"")
In the formula above, if errors are found, the cell will remain blank. If you want specific text inserted, the double quotes would be replaced with the desired text enclosed in quotes. "the text you want"
Do either of the formulas above work for you?
Kelly

Yes, the first one worked perfect! Thank you, again!

@Kelly Moore This was a great thread! I'm hoping you might have a fix for me. I have a similar situation, however I'm wanting to return multiple rows from the source sheet into what would be NewtoSmartsheet's initiative column. Is there a way to do that that you're aware of?

@Kelly Moore Right after I posed the question I discovered how to do it with a JOIN COLLECT formula. Thanks!

Fantastic, that is the trick! In case you didn't see it and need it, one can use the line break CHAR(10) as the delimiter (no quotes around it) and text wrap the column so that the answers appear stacked. Good job on your Join/Collect.
Help Article Resources
Categories
Check out the Formula Handbook template!