Domenic:

Thanks for your reply. As I mentioned, this is over my head. My function
experience has been limited to IF statements. In the first formula, is
"Sheet" a range name? My lookup value is the index number 1 which could be
on any sheet 1-10 and the tables are in sheets 1 - 10. I tried the first
formula and received #N/A. I will try the second formula and check back with
you.

Thanks again,
Laura

"Domenic" wrote:

Assuming that B1 on Sheet11 contains your lookup value, and Sheet1
through Sheet10 contain your lookup tables, try...

=VLOOKUP(B1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("Sheet"&ROW(INDI
RECT("1:10"))&"!A2:A100"),B1)0,0)&"!A2:F100"),6,0 )

or

=VLOOKUP(B1,INDIRECT("'"&INDEX($A$1:$A$10,MATCH(TR UE,COUNTIF(INDIRECT("'"
&$A$1:$A$10&"'!A2:A100"),B1)0,0))&"'!A2:F100"),6, 0)

....where A1:A10 on Sheet11 contains your list of sheet names. Both
formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
Adjust the ranges (A2:A100 and A2:F100) accordingly.

Hope this helps!

In article ,
"Laura" wrote:

I'm in over my head. Any help would be appreciated.

I have a workbook with 11 worksheets. I need to look for a value in column
A within the first 10 worksheets and return the corresponding value from
column F to worksheet number 11. Column A contains both text and numbers.

Column A Column F
Product Units
5 200
1 100
3 12
7 50

The numbers in column A are indexes from drop down boxes.

Please let me know if you need further info. Thank you for any help you can
give.

Laura