VLOOKUP Across Several Sheets

Hi, a tricky question I didn't know how to summarise in the title ...

I have 50 sheets. In each sheet there is a table with 2 collumns. The first collum is filled with names and the secon with ages. Each sheet is dedicated to a different city. I would like to know whether it is possible to "forge" a formula capable of summing the result of VLOOKUP from each sheet so that I can sum all ages that maches some name criteria. In a semi-english syntax would be something like this:

SUM(Sheet1:Sheet50!VLOOKUP("George";A1:B100;1;FALSE))

or

SUM(VLOOKUP("George";Sheet1:Sheet50!A1:B100;1;FALSE))

Is it somehow possible ? maybe using arrays ?

Thanks in advance for any support

Paolo

Italy

Hi,

You can't use any of the lookup functions in "3D", without a UDF or some other code.

I think that your best bet is to do the lookup on each sheet in a specific cell eg, Z100, and then sum that cell in "3D", eg =SUM(Sheet1:Sheet5!Z100)

by far the easiest would be to do a sumif on all sheets then sum3d the totals
you can enter the same formula in all sheets at once by selecting all sheets required then enter the formula in one of them it will be replicated in all the others
see attached formulas in yellow cells. but are you sure you want to sum ages or count them?