vlookup multiple worksheets
 

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

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

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

In article ,
"Laura" wrote:

Domenic:

Thanks for your reply.

You're very welcome!

In the first formula, is "Sheet" a range name?

No, it's used to reference your 10 worksheets.

My lookup value is the index number 1 which could be
on any sheet 1-10 and the tables are in sheets 1 - 10.

Can you confirm the range for your lookup table? Also, are they all
located in the same columns for each worksheet?

I tried the first
formula and received #N/A. I will try the second formula and check back with
you.

If your sheets 1 - 10 are named Sheet1, Sheet2, Sheet3, through Sheet10,
then the first formula would suffice.

YES!!!!! The first formula worked! Someday I'll understand what happened.

Thanks again,
Laura

"Domenic" wrote:

In article ,
"Laura" wrote:

Domenic:

Thanks for your reply.

You're very welcome!

In the first formula, is "Sheet" a range name?

No, it's used to reference your 10 worksheets.

My lookup value is the index number 1 which could be
on any sheet 1-10 and the tables are in sheets 1 - 10.

Can you confirm the range for your lookup table? Also, are they all
located in the same columns for each worksheet?

I tried the first
formula and received #N/A. I will try the second formula and check back with
you.

If your sheets 1 - 10 are named Sheet1, Sheet2, Sheet3, through Sheet10,
then the first formula would suffice.

Domenic:

Sorry to bother you again, but I have one more question. How do I keep the
formula from returning #N/A when the value is not found?

Thanks,
Laura

"Domenic" wrote:

In article ,
"Laura" wrote:

Domenic:

Thanks for your reply.

You're very welcome!

In the first formula, is "Sheet" a range name?

No, it's used to reference your 10 worksheets.

My lookup value is the index number 1 which could be
on any sheet 1-10 and the tables are in sheets 1 - 10.

Can you confirm the range for your lookup table? Also, are they all
located in the same columns for each worksheet?

I tried the first
formula and received #N/A. I will try the second formula and check back with
you.

If your sheets 1 - 10 are named Sheet1, Sheet2, Sheet3, through Sheet10,
then the first formula would suffice.

In article ,
"Laura" wrote:

Domenic:

Sorry to bother you again, but I have one more question.

No problem...

How do I keep the formula from returning #N/A when the value is not
found?

One option...

Enter the following formula in a cell, let's say C1:

=SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT( "1:10"))&"!A2:A100"),B1
))

....where B1 contains your lookup value.

Enter the following formula in another cell, let's say D1:

=IF(N(C1),VLOOKUP(B1,INDIRECT("Sheet"&MATCH(TRUE,C OUNTIF(INDIRECT("Sheet"
&ROW(INDIRECT("1:10"))&"!A2:A100"),B1)0,0)&"!A2:F 100"),6,0),"")

....confirmed with CONTROL+SHIFT+ENTER.

Another option...

Use conditional formatting to hide the errors. Assuming that the
formula is entered in D1:

1) Select D1

2) Format Conditional Formatting Formula Is

3) Enter the following formula:

=ISNA(D1)

4) Choose 'White' as your font colour

5) Click Ok

Hope this helps!