need help for use of nested INDIRECT function

[fmfred]

I've been working on this multi-sheet, extra-credit homework assignment for a University Excel course and the first problem requires the creation of a complex retrieval function that contains a nested INDIRECT function.

I have created a function that achieves the end result, but I can't figure out how to utilize the INDIRECT function, I get errors every time I do.

Here's the complex retrieval function I created:
=IF(INDEX(IssueList,Reason,MATCH(Dept,Deps,0)+1)<>0,INDEX(IssueList,Reason,MATCH(Dept,Deps,0)+1),"")

And here is the assignment instruction:
In Issue column, enter a retrieval function that returns the issue from the table in the IssueList worksheet. Use an INDEX function that will use a nested INDIRECT reference to the Dept named range listed in column C, and use the Reason field in column B as the row number to return the department name in the referenced named range. Nest the function inside an IF function so that issues currently displaying as a 0 will display as a blank cell. Resize the column width as needed.

Any help will be much appreciated!

[6StringJazzer]

We do not do your homework for you. If you have made progress and have a specific question about an Excel feature that is giving you trouble, we are happy to help. But please do not post your assignment and ask us to complete it for you.

If you provide your file, we might be able to give you some pointers as to what direction to go, or get you over a sticking point. But you'll need to ask a more specific question. It's impossible to understand your formula or the assignment without seeing how the file is set up. The paper clip icon does not work for attachments. Instead, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

(Also, IMHO this is a terrible assignment. Using nested INDIRECT is not particularly friendly with how the human brain works. I am a software developer and nested indirection in any context confuses even the best of us.)

[fmfred]

I figured it out. =IF(INDEX(INDIRECT(Dept),Reason)<>0,INDEX(INDIRECT(Dept),Reason),"")

Thanks for the tips, I didn't know we could upload our spreadsheets for inspection.

I agree with you that this assignment is terrible, but I think that was the assignment creator's intent -- it's an extra credit assignment. There were a couple hundred steps and this was the only one I was hung up on.

[6StringJazzer]

Interesting. That is not what I would call "nested INDIRECT". I would probably have designed this using a named formula instead of using INDIRECT, although I don't know what the name Dept refers to.

Good luck with your class!

[fmfred]

Thank you, and I hope it's considered "nested". In this case our text book would consider this nested since the INDIRECT is nested inside of =IF( and =(INDEX

[6StringJazzer]

I am sure that what you did is correct, I am not doubting that. Usually "nested" means "one inside another of the same type" like

=INDIRECT("B" & B1 & ":C" & INDIRECT(A1))

I would not have used the same wording as your instructor, but it's debatable.