Need help with INDIRECT and VLOOKUP

djl0525

Well-known Member
Joined
Dec 11, 2004
Messages
1,240
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top