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

IF Nested Error Help

Copper Contributor

Ok, so I have searched this site and must have checked out 20 different discussions in regard to the IF function and alternatives, however it seems that in most instances it is for financial. I am trying to use it to return specific information. I am attempting to develop a QA/QI form for my 911 Center in excel and need the formula to read a dropdown cell and reference sheet 2 to pull a specific line. There are 128 items in the dropdown for the IF function to read from, I know I can split it into 2 documents to make it work but was hoping that there might be someone here that can help me figure out a work around. I have attached a copy of document (had to use google docs since I cant upload the excel document so lost some functionality of the document like dropdown in C6) to provide a clear picture of what we are working with. I am open to all suggestions.

Sheet 1 Cell C6 is the dropdown of all the Call Types and pulls from Sheet 2 Cells M1 through M128
Sheet 1 Cells A19, A21, A23, A25, A27, and A29 are the required questions that the Call Taker should be asking (Complete formula is in A19 with ' in front of =)
Sheet 2 Cells A1 through A1024 is all the information that should be pulled based on what is selected in Sheet 1 Cell C6
Sheet 2 Cells M1 through M128 are all the Call Types we utilize


https://docs.google.com/spreadsheets/d/1W_0ggaMjubXgL7YZ426o7lKMo0j4eCSrkFR56WrjVnE/edit?usp=sharing

I know this is a lot of data to try and pull but I am at a loss for a simpler way to organize it.

2 Replies
best response confirmed by Grahmfs13 (Microsoft)
Solution

@Brett620 

=INDEX(Sheet2!A:A,MATCH($C$5,Sheet2!A:A,0)+1)

Does this return the expected result? This is the formula in cell A19 the formulas for A21, A23, A25, A27 and A29 are in the attached file.

@OliverScheurich 

 

That does, thank you very much! I feel like I have used this formula before and appreciate you "reminding" me how it works.

1 best response

Accepted Solutions
best response confirmed by Grahmfs13 (Microsoft)
Solution

@Brett620 

=INDEX(Sheet2!A:A,MATCH($C$5,Sheet2!A:A,0)+1)

Does this return the expected result? This is the formula in cell A19 the formulas for A21, A23, A25, A27 and A29 are in the attached file.

View solution in original post