Question

I have been trying to work on this for a while now, and this is on excel

In cell H10, enter an INDEX function that will use a nested INDIRECT reference to the Dept named range listed in column C (C10), and use the Reason field in column B (B10) as the row number to return for 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.

** I got this much of the formula

=INDEX(INDIRECT("Dept"),Reason)

but it only recalls and displays the Dept column contents. I need it to recall the Dept and display the Reason, I think? Then at that point, how would I get this into an IF statement?

I feel like I dont understand the instructions, and I know I am not that great on excel.

Answer 1

=IF(INDEX(INDIRECT(C10), B10)=0,"",INDEX(INDIRECT(C10), B10))

Or

=IF((INDEX((INDIRECT(Dept,TRUE)),Reason))="","",(INDEX((INDIRECT(Dept,TRUE)),Reason)))

Step-by-step explanation:

Given

Your formula:.

=INDEX(INDIRECT("Dept"),Reason)

Dept column = C (C10)

Reason field = B (B10)

The issue with your formula is that you failed to include a statement to test the falsity of the first condition; in other words, if your if statement is not true, what else should the formula do.

The question says that

"Nest the function inside an IF function so that issues currently displaying as a 0 will display as a blank cell" this means that

if the INDEX() function returns 0, a blank should be displayed in H10 blank, instead.

So, the right formula both of these two. You can use any of them

1. =IF(INDEX(INDIRECT(C10), B10)=0,"",INDEX(INDIRECT(C10), B10))

2. =IF((INDEX((INDIRECT(Dept,TRUE)),Reason))="","",(INDEX((INDIRECT(Dept,TRUE)),Reason)))

The two does the same function; the only difference is that

(1) considers the cell itself while (2) considers the contents of the cell.

The analysis of both is that

They both use a nested indirect reference to check for the content of cells displaying 0.

The first if checks for the above mentioned; if yes, cell H10 is made to display a blank else it's original content is displayed.