excel+training - excel embedding index function

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel+training - Excel: Embedding a Index Function and If Statement Function

excel+training - Excel: Embedding a Index Function and If Statement Function

resolvedResolved · Low Priority · Version Standard

Hina has attended:
Project Intro Intermediate course
Excel Advanced course

Excel: Embedding a Index Function and If Statement Function

Hello,

I hope you can help me.

I have been creating a spreadsheet that helps me keep track of interactions between people along with dates. My spreadsheet has a column which i can mark 'complete' or 'Not Complete' depending on when that interaction needs following up or if it has been completed. What i need help with is look up the Complete or Not Complete values and then putting it on a status sheet.

I have managed to look up 'Not Completed' work but when all the cells in the column say complete I get an error "#N/A". This is the formulea i put in:

=INDEX(Follow_Up_Complete,MATCH("Not Complete",Follow_Up_Complete,0),1)

"Follow_Up_Complete" are named cells that contain the complete or not complete fields. I just named them to make it easier for me.

How do I handle the exception to the rule so that the result does not say "#N/A" Can I use an IF statement with an INDEX/MATCH function? How would i do that?

I look forward to hearing back from you.

Kind Regards,

Hina Patel

RE: Excel: Embedding a Index Function and If Statement Function

It's a bit of a messy formula, but the best way I find to get rid of #N/As is to do something like this:

(untested)

=if(isna(INDEX(Follow_Up_Complete,MATCH("Not Complete",Follow_Up_Complete,0),1),"",INDEX(Follow_Up_Complete,MATCH("Not Complete",Follow_Up_Complete,0),1)))

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Use the SUBTOTAL function in Excel

You can create subtotals in your spreadsheet using the SUBTOTAL function, which looks like this:

=SUBTOTAL(9,cell:cell)

9 represents the function being used (SUM), followed by the range of cells the function is operating on.

The neat thing about using the Subtotal function is that if you have used it several times in the same column or row, clicking on the AutoSum button at the end of the column or row will make Excel add only the results of cells containing the Subtotal function in that column or row.

View all Excel hints and tips


Server loaded in 0.13 secs.