Formula Help

swaldon2

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

Paul Newcome

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

Paul Newcome

Are you able to provide some screenshots for context?

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

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

swaldon2

@Paul Newcome Yes exactly

Paul Newcome

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

swaldon2

@Paul Newcome That worked, thank you!

Paul Newcome

Happy to help. 👍️