Vlookup With Array Between Multiple Worksheets

[Jonny757]

Hello there,

I know I've not been very helpful with the last questions I've posted on here, I haven't had the time to properly explain the problems I'm having with Excel and half the time I'm no sure how to explain :/

Anyway, here's an important issue which I haven't been able to solve. I have a 'Summary' worksheet where I want to pull a certain cell from each worksheet in my workbook (E15, same location on sheets named '1801' to '4990'). I have a 'lookup code' in the first column of all these sheets I want to look in, with the codes relating to the first column of my 'Summary' sheet.

Therefore, I assumed a working formula would be:

=VLOOKUP(A3,'1801:4990'!$A$2:$K$20,5) - the number I want to pull is in the fifth column but I only want it to be pulled if A? column contains A3 in the 'Summary' sheet.

The problem may be that '1801:4990' multiple sheets, or that '5' for the column that is should retrieve from doesn't refer to 'the fifth column of the row, in the sheet, which has corresponding A values.

I haven't explained it very, I will post it up later to show you what I mean.

Thank you very much for any advice :}

[Richard Buttrey]

Hi,

You can't use VLOOKUP across several sheets. If there were are you saying that the value you're trying to look up only exists on ONE of those sheets?
If not and the lookup value is on more than one how would a function know which sheet to use?

Are you in fact wanting to SUM the values from all sheets.

We do need to see your workbook in order to comment more.

[Jonny757]

Yes, the number which I may looking up will only be in ONE 'E15' cell in the workbook.

I wish to search most, but not all, of the worksheets for the E15 with containing the specified value. The only ones I don't want to search are the first three sheets, which include the sheet I will write the formula in.

I don't want to find a SUM between sheets, merely retrieve a figure from that sheet.