Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

trying to keep the box blank unless the other boxes used are filled

Copper Contributor

trying to keep the box blank unless the other boxes used are filled

Hello,

I am using this formula which works but I keep getting the "#N/A" in the box until an adjacent box is filled in to give the formula answer. Can someone please assist with having the formula leave the box blank unless the adjacent box is filled, thank you.

Names are used to fill in the boxes which will automatically trigger the box to fill in with a name of the Point of Contact and also a phone number of the POC in the box next to it using another formula in that box.

=INDEX(\$AF\$2:\$AK\$19,MATCH(\$I11,\$AG\$2:\$AG\$19,0),MATCH(\$J\$1,\$AF\$1:\$AK\$1,0))

Thank you for any assistance

2 Replies
best response confirmed by Gonzo45 (Copper Contributor)
Solution

Re: trying to keep the box blank unless the other boxes used are filled

=IFERROR(INDEX(\$AF\$2:\$AK\$19,MATCH(\$I11,\$AG\$2:\$AG\$19,0),MATCH(\$J\$1,\$AF\$1:\$AK\$1,0)),"")

You can wrap the formula into IFERROR.

Re: trying to keep the box blank unless the other boxes used are filled

Thank you, I am not versed enough to know how to use that iferror formula so I appreciate you helping me out.
1 best response

Accepted Solutions
best response confirmed by Gonzo45 (Copper Contributor)
Solution

Re: trying to keep the box blank unless the other boxes used are filled

=IFERROR(INDEX(\$AF\$2:\$AK\$19,MATCH(\$I11,\$AG\$2:\$AG\$19,0),MATCH(\$J\$1,\$AF\$1:\$AK\$1,0)),"")

You can wrap the formula into IFERROR.