Need help with INDIRECT and VLOOKUP

[djl0525]

Workload Detail!E11 contains "Sub Total 01:"
column E contains Sub Total 01:, Sub Total 02:, Sub Total 03:, etc.
Workload Detail!G5 contains "B Fund"

Workload by AH!A7 contains "01" (the sub total number)
Workload by AHG5 contains "B Fund"

In Workload by AH!G7 (the intersection of A7 and G5) I need a formula that will lookup the Sub Total 01 for B Fund on the Workload Detail! sheet. Right now the answer is in Workload Detail!G11 (the intersection of E11 and G5), but as I refresh the data over time it won't always be in that cell.

I think my formula needs to use the INDIRECT and the VLOOKUP functions, but I can't figure it out. I hope this makes sense. Any help will be appreciated.

DJ

 

[Andrew Poulsom]

Try something like:

=INDEX('Workload Detail'!$E$5:$I$13,MATCH("Sub Total "&TEXT($A7,"00")&":",'Workload Detail'!$E$5:$E$13,FALSE),MATCH(G$5,'Workload Detail'!$E$5:$I$5,FALSE))

which can be copied down and across.

You may want to expand the ranges E5:I13,E5:E13 and E5:I5.

 

[Fergus]

Try : =VLOOKUP("Sub Total "&A7,Workload Detail!$E$11:$P$19,MATCH(G5,Workload Detail!$E$5:$P$5,0),0) in cell G7 of Workload by AH

Adjust ranges to suit

HTH