Formula Help

swaldon2
swaldon2 ✭✭✭✭✭
edited 03/20/24 in Formulas and Functions

I need a formula that pulls the data from the column if that cell is not empty. (Ex; If 1 is empty but 2 is filled, pull data from field 2).

  1. As-Built Production Yield
  2. Final Production Yield
  3. 90% Production Yield
  4. 60% Production Yield
  5. 30% Production Yield
  6. Total Production Yield Target

My current formula is below but it does not work.

=IF(NOT(ISBLANK(INDEX({As-Built Production Yield}, MATCH([Project ID]@row, {Engineering ID})))), INDEX({Final Production Yield}, MATCH([Project ID]@row, {Engineering ID})), INDEX({90% Production Yield}, MATCH([Project ID]@row, {Engineering ID}))), INDEX({60% Production Yield}, MATCH([Project ID]@row, {Engineering ID})), INDEX({30% Production Yield}, MATCH([Project ID]@row, {Engineering ID})), INDEX({Total Production Yield Target}, MATCH([Project ID]@row, {Origination ID}))

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ok. The first thing to do is make sure the INDEX/MATCH syntax is correct.

    =IFERROR(INDEX({Column To Pull From}, MATCH([Column To Match On]@row, {Column To Match On}, 0)), "")


    Make sure to use the zero in the final portion of the MATCH function to look for an exact match. Otherwise your results could get a bit wonky.

    Write one for each of the 6 columns you want to pull in. I will just use "index_match_#" throughout the rest of this to indicate where each one goes instead of trying to type everything out.


    To pull from each one in a specific order, we use a nested IF.

    =IF(index_match_6 <> "", index_match_6, IF(index_match_5 <> "", index_match_5, IF(index_match_4 <> "", index_match_4, IF(index_match_3 <> "", index_match_3, IF(index_match_2 <> "", index_match_2, index_match_1)))))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide some screenshots for context?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • swaldon2
    swaldon2 ✭✭✭✭✭

    @Paul Newcome There is sensitive data / three sheets I am referencing within this formula. But here is a breakdown that I think would help. So all the values in the list below are columns on their respective sheets and each row on these sheets represents a project. On the intake sheet (SHEET 1) there is a column that I need that looks at SHEET 2 and SHEET 3 to produce a value based on each project. Each project has a unique ID (PROJECT ID) which is on all three sheets.

    1. As-Built Production Yield - SHEET 2
    2. Final Production Yield - SHEET 2
    3. 90% Production Yield - SHEET 2
    4. 60% Production Yield - SHEET 2
    5. 30% Production Yield - SHEET 2
    6. Total Production Yield Target - SHEET 3

    I need a formula that pulls a value from the 6 columns above based on the project ID's when a column is not blank. So if project ABC has the 6-4 columns (listed above) filled out, it will show the 60% production yield value for that project. But if project ABC has the 1-6 columns filled out, it will show the As-built production yield value.

    Let me know if you need more.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    So basically you need to collect the 6 columns for a single project then pull the last filled in (starting at 6 and going down to 1)?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ok. The first thing to do is make sure the INDEX/MATCH syntax is correct.

    =IFERROR(INDEX({Column To Pull From}, MATCH([Column To Match On]@row, {Column To Match On}, 0)), "")


    Make sure to use the zero in the final portion of the MATCH function to look for an exact match. Otherwise your results could get a bit wonky.

    Write one for each of the 6 columns you want to pull in. I will just use "index_match_#" throughout the rest of this to indicate where each one goes instead of trying to type everything out.


    To pull from each one in a specific order, we use a nested IF.

    =IF(index_match_6 <> "", index_match_6, IF(index_match_5 <> "", index_match_5, IF(index_match_4 <> "", index_match_4, IF(index_match_3 <> "", index_match_3, IF(index_match_2 <> "", index_match_2, index_match_1)))))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • swaldon2
    swaldon2 ✭✭✭✭✭

    @Paul Newcome That worked, thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!