Formula Help

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}))


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)))))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!