Multiple Sheet Lookup

M22RDY

New Member
Joined
Oct 3, 2006
Messages
26
I've been trying to do a VLOOKUP against multiple sheets and keep getting a #REF error.

I have a spreadsheet with about 40+ worksheets, each with seperate information. I want to have a front sheet that with lookup all the 40+ sheets for a specific month and return the rest of the row (7 columns).

example:

I type March, it searches the 40 worksheets for March within column A, and return the 7 fields to the right. Note: A worksheet can have more than 1 'March' within column A.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

viquarshaikh

Active Member
Joined
Mar 21, 2006
Messages
444
Welcome to the Board M22RDY

Looks like you're in for an painfully slow formula. Let's see it this way.
You're searching for a particular value (i.e. "Looking it Up") and this value could exist on any one ( or more )of the 40+ sheets.

Also on a single sheet a single value (e.g. March) could appear several times.

I'm unsure as to how VLOOKUP would help you in a situation like this. Unless you decide to have around 40 nested ifs or you decide to have a flag which atleast narrows the number of Sheeets to lookup for a particular value to a particular sheet.
Even then VLOOKUP would end up returning only the first value it encounters.

Maybe if you give a better explanation of what you're trying to achieve, some of us here could offer alternatives.
 

Forum statistics

Threads
1,176,627
Messages
5,904,112
Members
435,074
Latest member
McKay_S

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
Top