Formula Help
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).
- As-Built Production Yield
- Final Production Yield
- 90% Production Yield
- 60% Production Yield
- 30% Production Yield
- 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
-
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!
Answers
-
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!
-
@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.
- As-Built Production Yield - SHEET 2
- Final Production Yield - SHEET 2
- 90% Production Yield - SHEET 2
- 60% Production Yield - SHEET 2
- 30% Production Yield - SHEET 2
- 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.
-
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!
-
@Paul Newcome Yes exactly
-
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!
-
@Paul Newcome That worked, thank you!
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!