VLOOKUP over multiple sheets
matching value in cell e4 look in cell E1 of every tab
Which is it, E1 or E4? I used E4 in the formula.
Biff
"Biff" wrote in message
...
Hi!
One way:
Create a list of the sheet names:
H1 = Sheet2
H2 = Sheet3
H3 = Sheet4
H4 = Sheet5
Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just ENTER):
=INDIRECT("'"&INDEX(H$1:H$4,MATCH(TRUE,COUNTIF(IND IRECT("'"&H$1:H$4&"'!E4"),A1)0,0))&"'!AC1")
Biff
"Jaisenm" wrote in message
...
I have a workbook with a summary tab and one tab for ever week.
I need to write a formula that looks for a value from column a, find the
sheet with a matching value in cell e4 and returns the value of cell ac1
of
the worksheet.
This formula will live in column b of the summary sheet.
Example:
Summary Tab
A B
1 19-Jul 19,000(formula result)
2 16-Jul
3 23-Jul
4 30-Jul
Data Tab 1
E AC
1 9-Jul 19,000
It will need to look in cell E1 of every tab in the work book (52 tabs)
for
the value found in $a1.
|