How do i use the INDEX/MATCH formula to populate a cell based on a drop-down selection?

NewtoSmartsheet

I have two sheets, one has my data that has the drop-down 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.

NewtoSmartsheet

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.

Kelly Moore

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

Kelly Moore

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

Kelly Moore

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 coping-pasting the above formula into your sheet.

Let me know if this works for you

Kelly

NewtoSmartsheet

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"

Kelly Moore

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 range-number 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

NewtoSmartsheet

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.

NewtoSmartsheet

Sorry, the formula begins with =INDEX(COLLECT

Kelly Moore

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.

NewtoSmartsheet

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.

Kelly Moore

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

NewtoSmartsheet

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

Kelly Moore

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

NewtoSmartsheet

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

Nate H

@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?

Nate H

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

Kelly Moore

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.