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

Shawn Burnett ✭✭✭✭✭
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.
0 
KDM ✭✭✭✭✭
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([email protected],6),@cell)), 1)
cheers
Kelly
0 
KDM ✭✭✭✭✭
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}, [email protected]), 1),"")
=IFERROR(INDEX(COLLECT({source sheet Initiative}, {source sheet Initiative Year}, [FY Year]@row, {source sheet Goal}, CONTAINS(LEFT([email protected],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
0
Answers
Hey @Shawn Burnett
Try this
=INDEX(COLLECT({source sheet Initiative}, {source sheet Initiative Year}, [FY Year]@row, {source sheet Goal}, [email protected]), 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}, [email protected]), 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}, [email protected]), 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([email protected],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}, [email protected]), 1),"")
=IFERROR(INDEX(COLLECT({source sheet Initiative}, {source sheet Initiative Year}, [FY Year]@row, {source sheet Goal}, CONTAINS(LEFT([email protected],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!