I am trying to use the VLOOKUP function over multiple worksheets, can this be
done on Excel 2003?

I am trying to automatically retrieve data that is dependent on previously
entered fields in drop down lists that I have created.

Regards,

A semi-exoctic formula from Peo S a few years ago, this looks up across
eight sheets. It is an array-entered formula... CTRL+SHIFT+ENTER

If you wnt to tackle this I will help you, I don't completely understand the
formula but I believe I can guide you through it to lookup over many
worksheets. (formula is all one one line in both cases, wrap kinda sucks
here on my screen)

=VLOOKUP(A1,INDIRECT("'"&INDEX({"Sheet1";"Sheet2"; "Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Shee t8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3"; "Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A 2:A200"),A1)0),0))&"'!A2:C200"),2,0)

Or using a named range of the sheets instead of each sheet name...

=VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A1)0) ,0))&"'!A2:C200"),3,0)

HTH
Regards,
Howard

"cp402" wrote in message
...
I am trying to use the VLOOKUP function over multiple worksheets, can this
be
done on Excel 2003?

I am trying to automatically retrieve data that is dependent on previously
entered fields in drop down lists that I have created.

Regards,

I don't completely understand the formula

It's actually quite simple and your reaction will be: "Of course, it's so
simple!".

The first thing that happens is the COUNTIF checks each sheet to see if the
lookup_value exists on any of the sheets.

COUNTIF(Sheet1!A2:A200,A1)0
COUNTIF(Sheet2!A2:A200,A1)0
COUNTIF(Sheet3!A2:A200,A1)0
etc
etc

If the lookup_value exists on any of the sheets then one of the above
expressions will return TRUE:

COUNTIF(Sheet1!A2:A200,A1)0 = FALSE
COUNTIF(Sheet2!A2:A200,A1)0 = FALSE
COUNTIF(Sheet3!A2:A200,A1)0 = TRUE
etc
etc

The double unary -- will convert the logical TRUE or FALSE to 1 or 0
respectively:

--(COUNTIF(Sheet1!A2:A200,A1)0) = 0
--(COUNTIF(Sheet2!A2:A200,A1)0) = 0
--(COUNTIF(Sheet3!A2:A200,A1)0) = 1
etc
etc

MATCH then looks for the first instance of 1 and if present returns it's
relative position within the array of results from the COUNTIF functions:

MATCH(1,{0;0;1},0) = 3 (the lookup_value 1 is found at relative position 3).

This result is then passed to the INDEX function:

INDEX({"Sheet1";"Sheet2";"Sheet3"},3)

That tells INDEX we want the 3rd value of the indexed array Sheet1, Sheet2,
Sheet3

INDEX({"Sheet1";"Sheet2";"Sheet3"},3) = Sheet3

This result is then added to a string of concatenation processes:

"'"&"Sheet3"&"'!A2:C200" = 'Sheet3'!A2:C200 (as a TEXT string)

Since we're "building" the range reference to be used in the VLOOKUP
function, the "built" reference will be a *TEXT* string that looks like a
valid range reference. So, we have to convert this *TEXT* string into a
valid range reference that the VLOOKUP can use. We do this using the
INDIRECT function.

INDIRECT("'"&"Sheet3"&"'!A2:C200") = 'Sheet3'!$A$2:$C$200 as a valid range
reference that VLOOKUP can use:

=VLOOKUP(A1,'Sheet3'!$A$2:$C$200,2,0)


exp101
--
Biff
Microsoft Excel MVP


"L. Howard Kittle" wrote in message
...
A semi-exoctic formula from Peo S a few years ago, this looks up across
eight sheets. It is an array-entered formula... CTRL+SHIFT+ENTER

If you wnt to tackle this I will help you, I don't completely understand
the formula but I believe I can guide you through it to lookup over many
worksheets. (formula is all one one line in both cases, wrap kinda sucks
here on my screen)

=VLOOKUP(A1,INDIRECT("'"&INDEX({"Sheet1";"Sheet2"; "Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Shee t8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3"; "Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A 2:A200"),A1)0),0))&"'!A2:C200"),2,0)

Or using a named range of the sheets instead of each sheet name...

=VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A1)0) ,0))&"'!A2:C200"),3,0)

HTH
Regards,
Howard

"cp402" wrote in message
...
I am trying to use the VLOOKUP function over multiple worksheets, can this
be
done on Excel 2003?

I am trying to automatically retrieve data that is dependent on
previously
entered fields in drop down lists that I have created.

Regards,

Numeric value:

For Sheet1,...,Sheet4

=SumProduct(Sumif(INDIRECT("Sheet"&Row(1:4)&"!A2:A 6"),$A
$2,INDIRECT("Sheet"&Row(1:4)&"!B2:B6")))

http://boisgontierjacques.free.fr/fi...rcheV3DNum.xls

AlphaNumeric value:

=VlookUp(A2,INDIRECT("Sheet"&Match(True,
(CountIf(INDIRECT("Sheet"&Row(1:4)&"!A2:B6"),A2)0 ),0)&"!A2:B6"),
2,False)
valid with Shift+Ctrl+Enter

http://boisgontierjacques.free.fr/fi...heV3DAlpha.xls

http://boisgontierjacques.free.fr/pa...3D.htm#Rechv3D

JB
http://boisgontierjacques.free.fr/

On 11 mar, 01:33, cp402 wrote:
I am trying to use the VLOOKUP function over multiple worksheets, can this be
done on Excel 2003?

I am trying to automatically retrieve data that is dependent on previously
entered fields in drop down lists that I have created.

Regards,