Searching HLOOKUP in external workbook accross multiple sheets

bennymc

New Member
Joined
Jun 21, 2012
Messages
6
Hi there Excel masters!

Ok, here goes. Please excuse me, I'm fairly new to this.

I'm trying to create a formula for work that will look up information on an external spreadsheet based on conditions. I want to be able so search a date in one cell, then a department in another cell then display (in the formulated 3rd cell) the result based on the 5th cell down from the one it found. I'll try and explain it a bit easier...

The spreadsheet ROW I'm editing looks like this: [date] [department] [result]. The target information ROW looks like [25/07/12] [credit] [sales] [service] [internet] - these cells have a numeric value 5 cells down that I want. So, if I searched for '25/07/12' in the date cell and 'sales' in the department cell then I want the result to be the value of the celll '5 cells down' from the sales column in the external spreadsheet.

Make sense? So far I I've tried HLOOKUP to find the specific cell which has been successful - =HLOOKUP(E25, '[Quarter 1 FY12.xls]2012 02 25'!$1:$6, 1, FALSE). But then I need to somehow nest perhaps OFFSET in the formula and incorporate the 'department' condition... I think.

The external workbook is called Quarter 1 FY12.xls and contains 13 'Sheets' (Hence 'Quarter 1' - it displays the the first 13 weeks in the year) and each sheet has 7 'dates' with the same format as mentioned above.

I hope someone can help!

P.S. I know little to nothing about VB, so for me is not an alternative - unless someone writes the code for me :P
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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