Need help with adding additional reference sheets to this formula.

Joyce W

Hello!

I have a sheet name "Process Issues" with a series of information in this sheet. I have 3 other sheets named: Deposit, Lending, Payments. I have set up that when the project ID from the "Process Issues" sheet, is entered in the applicable deposit, lending and payments sheet, that the fields are auto populated from the "Process Issues" sheet. This formula I have working.

I have a "Notes' column in each of the deposit, lending, and payments sheets. I want write a formula that would copy over the notes back to the "Process Issues" sheet based on the project ID from the Deposit, Lending and Payments sheet.

I am now stuck on this formula and I am only able to get the formula to work with just one sheet (lending), and I can't get the deposit and payments in the formula. How can I add the other two sheets into the formula? Any help would be appreciated! Thanks!

=INDEX({Lending Summary Data}, MATCH($[Project ID]@row, {Project ID}, 0), MATCH(Notes$1, {Lending Summary Header}, 0))

Paul Newcome

It is because you are not finishing out the IFERROR statements. You need to tell each one what to do if there is an error.

Paul Newcome

You would create separate INDEX/MATCH statements and "add" them together.

=INDEX/MATCH + CHAR(10) + CHAR(10) + INDEX/MATCH + CHAR(10) + CHAR(10) + INDEX/MATCH

Note: CHAR(10) is a line break. Using the wrap text feature should help keep things pretty clean.

Joyce W

@Paul Newcome : Thank you! My function and formula skills are not great. Either that I'm not understanding the format of the statement or I am doing something totally wrong.

This is what I have:

=INDEX({Lending Summary Data}, MATCH($[Project ID]@row, {Project ID LD}, 0), MATCH([Target End Date]$1, {Lending Summary Header}, 0)) + INDEX({Deposit Summary Data}, MATCH($[Project ID]@row, {Project ID DP}, 0), MATCH([Target End Date]$1, {Deposit Summary Header}, 0)) + INDEX({Payments Summary Data}, MATCH($[Project ID]@row, {Project ID PM}, 0), MATCH([Target End Date]$1, {Payments Summary Header}, 0))

Paul Newcome

Is that not working as expected? Are you getting an error message or an unexpected output?

Joyce W

Hi @Paul Newcome,

The error message I am getting is #UNPARSABLE.

This is the formula I have working: =INDEX({Lending Summary Data}, MATCH($[Project ID]@row, {Project ID LD}, 0), MATCH(Notes$1, {Lending Summary Header}, 0))

So I basically want to add this to the formula above make it one single formula:

=INDEX({Deposit Summary Data}, MATCH($[Project ID]@row, {Project ID DP}, 0), MATCH(Notes$1, {Deposit Summary Header}, 0))

Plus

=INDEX({Payments Summary Data}, MATCH($[Project ID]@row, {Project ID PM}, 0), MATCH(Notes$1, {Payments Summary Header}, 0))

Thanks!

Paul Newcome

Are you able to provide a screenshot of the formula actually in the sheet similar to the screenshot below?

Joyce W

@Paul Newcome: Thank you. Hope this screen shot will help!

This is the working formula:

=INDEX({Ops Lending Summary Data}, MATCH($[Project ID]@row, {Project ID LD}, 0), MATCH(Notes$1, { Ops Lending Header}, 0))

So I need the other sheets Deposit and Payments combined to the formula above

=INDEX({Deposit Summary Data}, MATCH($[Project ID]@row, {Project ID DP}, 0), MATCH(Notes$1, {Deposit Summary Header}, 0))

Plus

=INDEX({Payments Summary Data}, MATCH($[Project ID]@row, {Project ID PM}, 0), MATCH(Notes$1, {Payments Summary Header}, 0))

Paul Newcome

What about the formula you are getting the error on that is adding each of them together?

Joyce W

@Paul Newcome:

Paul Newcome

Try removing the + from the end or adding in the third INDEX.

Joyce W

@Paul Newcome : It's still not working. It's giving an error of either Invalid Operation or No Match.

Paul Newcome

Ok. In that case, try wrapping each INDEX in an IFERROR.

=IFERROR(INDEX(….., MATCH(…..), MATCH(…..)), "") + IFERROR(INDEX(….., MATCH(…..), MATCH(…..)), "") + IFERROR(INDEX(….., MATCH(…..), MATCH(…..)), "")

Joyce W

Hi @Paul Newcome :

I have tried the IFERROR in front of the index and get an error of "Incorrect Argument Set"

=IFERROR(INDEX({Ops Lending Summary Data}, MATCH($[Project ID]@row, {Project ID LD}, 0), MATCH(Notes$1, {Ops Lending Header}, 0))) + IFERROR(INDEX({Ops Deposit Summary Data}, MATCH($[Project ID]@row, {Project ID DP}, 0), MATCH(Notes$1, {Ops Deposit Header}, 0))) + IFERROR(INDEX({Ops Payment Summary Data}, MATCH($[Project ID]@row, {Project ID PM}, 0), MATCH(Notes$1, {Ops Payment Header}, 0)))

Paul Newcome

It is because you are not finishing out the IFERROR statements. You need to tell each one what to do if there is an error.

Joyce W

@Paul Newcome : Yes! It worked! Thank you so much for your help!

Paul Newcome

Happy to help. 👍️