Index, Match, and Indirect Problem

[smeye011]

Hi All,

I've been trying to solve this problem for a bit and figured I'd reach out for a little help.

I currently have two tabs - one called "utilization" which is a column filled with clinicians in column B and Prior Week's session count in Column D. The second tab, "Volume Report 2018", contains a list of clinicians in column B, with dates of the beginning of each week spanning on row 2 from columns C:FX.

My goal is to have column D in the "utilization tab" show the correct session count by clinician each week. I can manually do this as follows:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style> =(INDEX('Volume Report 2018'!C190:FX190,MATCH($K$1,'Volume Report 2018'!$C$2:$FX$2,0)),0)

In this instance, each clinician is given their own row in the volume report tab, so this clinician is record 190. The formula then looks at cell K1, which I have set to always show the previous week's starting date - then matches that with the corresponding week column in the volume report. This set up works, BUT, I'd like to be able to simply enter "160" in an adjacent cell, let's say E2, and have the formula replace C190:FX190 with C160:FX160 if I'd like to search another clinician on the fly. I know this will require some use of the indirect function, but i can't get the syntax correct.

Thanks in advance for any help!

 

[admiral100]

Hi,

Try this : E2 contain 160

=INDEX(INDIRECT("Volume Report 2018!C"&E2&":FX"&E2),MATCH($K$1,'Volume Report 2018'!$C$2:$FX$2,0))

 

[pgc01]

Hi

With A1=160 try:

=INDEX(INDEX('Volume Report 2018'!C:FX,A1,0),MATCH($K$1,'Volume Report 2018'!$C$2:$FX$2,0))

Remark: When possible avoid Indirect() as it's volatile.