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

04/02/21
Accepted

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.

Best Answers

  • Shawn BurnettShawn Burnett ✭✭✭✭✭
    Accepted Answer

    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.

  • KDMKDM ✭✭✭✭✭
    Accepted Answer

    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

  • KDMKDM ✭✭✭✭✭
    Accepted Answer

    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

Answers

  • KDMKDM ✭✭✭✭✭

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

    Let me know if this works for you

    Kelly

  • Shawn BurnettShawn Burnett ✭✭✭✭✭

    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"

  • KDMKDM ✭✭✭✭✭

    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

  • Shawn BurnettShawn Burnett ✭✭✭✭✭

    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.

  • Shawn BurnettShawn Burnett ✭✭✭✭✭

    Sorry, the formula begins with =INDEX(COLLECT

  • KDMKDM ✭✭✭✭✭
    edited 04/05/21

    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.

  • Shawn BurnettShawn Burnett ✭✭✭✭✭
    Accepted Answer

    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.

  • KDMKDM ✭✭✭✭✭
    Accepted Answer

    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

  • Shawn BurnettShawn Burnett ✭✭✭✭✭

    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?


  • KDMKDM ✭✭✭✭✭
    Accepted Answer

    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

  • Shawn BurnettShawn Burnett ✭✭✭✭✭

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

Sign In or Register to comment.