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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

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