vlookup multiple worksheets

[Guest]

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]

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(INDIRECT("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(TRUE,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!

 

[Guest]

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]

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.

 

[Guest]

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

Thanks again,
Laura

 

[Guest]

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]

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,COUNTIF(INDIRECT("Sheet"
&ROW(INDIRECT("1:10"))&"!A2:A100"),B1)>0,0)&"!A2:F100"),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!