Vlookup for 3 sheets

Dear All, pls help me.
I want to make a Vlookup, the data of Product est very big, Column A is
Item, Column B is Description, there have 3 Sheets for product ( Product01,
Product02 and Product03 ) .

I know just to do for Product01.
=VLOOKUP(A1,Product01!A:B,2,FALSE)

Pls help me the formula also for Product01!A:B, Product02!A:B and
Product03!A:B

Thanks so much.

May be this…

If you want to combine Vlookup for all the three sheets in one formula then
try the below:-

=IF(A1="","",IF(ISNA(VLOOKUP(A1,Product01!A:B,2,FA LSE)),"["&UPPER(A1)&" Not
Available In Product01
Sheet]",IF(VLOOKUP(A1,Product01!A:B,2,FALSE)=0,"[Description is blank for
"&UPPER(A1)&" In Product01 Sheet]","[Product01 :
"&VLOOKUP(A1,Product01!A:B,2,FALSE))&"]")&CHAR(10)&IF(ISNA(VLOOKUP(A1,Product02!A:B,2,FAL SE)),"["&UPPER(A1)&"
Not Available In Product02
Sheet]",IF(VLOOKUP(A1,Product02!A:B,2,FALSE)=0,"[Description is blank for
"&UPPER(A1)&" In Product02 Sheet]","[Product02 :
"&VLOOKUP(A1,Product02!A:B,2,FALSE))&"]")&CHAR(10)&IF(ISNA(VLOOKUP(A1,Product03!A:B,2,FAL SE)),"["&UPPER(A1)&"
Not Available In Product03
Sheet]",IF(VLOOKUP(A1,Product03!A:B,2,FALSE)=0,"[Description is blank for
"&UPPER(A1)&" In Product03 Sheet]","[Product03 :
"&VLOOKUP(A1,Product03!A:B,2,FALSE)&"]")))

If you want the individual Vlookup formula then try the below:
Product 01:-
=VLOOKUP(A1,Product01!A:B,2,FALSE)

Product 02:-
=VLOOKUP(A1,Product02!A:B,2,FALSE)

Product 03:-
=VLOOKUP(A1,Product03!A:B,2,FALSE)
--
Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"tran1728" wrote:

Dear All, pls help me.
I want to make a Vlookup, the data of Product est very big, Column A is
Item, Column B is Description, there have 3 Sheets for product ( Product01,
Product02 and Product03 ) .

I know just to do for Product01.
=VLOOKUP(A1,Product01!A:B,2,FALSE)

Pls help me the formula also for Product01!A:B, Product02!A:B and
Product03!A:B

Thanks so much.

Thanks for your reply Micky,
But it can't run ,
=VLOOKUP(A1,INDIRECT(IF(OR(A1=DAT1),"DATA1",IF(OR( A1=DAT2),"DATA2","DATA3"))),2,0)
the computer shows #N/A

Thanks


"מיכ�ל (מיקי) �בידן" wrote:

Define names referring the 3 data ranges.
DAT1, DAT2, DAT3 - for the column A ranges in each sheet.
[DAT1 for range col. A in sheet Product01 and so on for the other two sheets]
Define another 3 names referring the 3 data tables.
[DATA1 for the range A:B in sheet Product01 and so on for the other two
sheets]
[each range Consists 2 columns] while the names of the 3 sheets a
Product01, Product02, Product03 respectively - and try the following Array
formula:
{=VLOOKUP(A1,INDIRECT(IF(OR(A1=DAT1),"DATA1",IF(OR (A1=DAT2),"DATA2","DATA3"))),2,0)}
[You should NOT type the curly braces.
In order to confirm the formula, you will use the three key combination -
while holding, down, CTRL+SHIFT press ENTER instead of just pressing ENTER.
You will be able to identify an Array Formula, in the Formula Bar, if it is
confined in a pair of curly braces.]
Micky


"tran1728" wrote:

Dear All, pls help me.
I want to make a Vlookup, the data of Product est very big, Column A is
Item, Column B is Description, there have 3 Sheets for product ( Product01,
Product02 and Product03 ) .

I know just to do for Product01.
=VLOOKUP(A1,Product01!A:B,2,FALSE)

Pls help me the formula also for Product01!A:B, Product02!A:B and
Product03!A:B

Thanks so much.

Step 1 : Definie 3 Ranges.
Definie DAT1 for range col. A in sheet Product01, Definie DAT2 for range
col. A in sheet Product02, Definie DAT3 for range col. A in sheet Product03.

Step 2 : Definie 3 tables.
Definie DATA1 for range A:B in sheet Product01, Definie DATA2 for range A:B
in sheet Product02, Definie DATA3 for range A:B in sheet Product03.

Step 3 : Type the formula in sheet "Check Product"
Type the formula in the Range B1
=VLOOKUP(A1,INDIRECT(IF(OR(A1=DAT1),"DATA1",IF(OR( A1=DAT2),"DATA2","DATA3"))),2,0)

Is Right ?




"מיכ�ל (מיקי) �בידן" wrote:

It should and will run [as it do for me for the last 15 years] if you'll
follow ALL my instructions word by word.
Micky


"tran1728" wrote:

Thanks for your reply Micky,
But it can't run ,
=VLOOKUP(A1,INDIRECT(IF(OR(A1=DAT1),"DATA1",IF(OR( A1=DAT2),"DATA2","DATA3"))),2,0)
the computer shows #N/A

Thanks


"מיכ�ל (מיקי) �בידן" wrote:

Define names referring the 3 data ranges.
DAT1, DAT2, DAT3 - for the column A ranges in each sheet.
[DAT1 for range col. A in sheet Product01 and so on for the other two sheets]
Define another 3 names referring the 3 data tables.
[DATA1 for the range A:B in sheet Product01 and so on for the other two
sheets]
[each range Consists 2 columns] while the names of the 3 sheets a
Product01, Product02, Product03 respectively - and try the following Array
formula:
{=VLOOKUP(A1,INDIRECT(IF(OR(A1=DAT1),"DATA1",IF(OR (A1=DAT2),"DATA2","DATA3"))),2,0)}
[You should NOT type the curly braces.
In order to confirm the formula, you will use the three key combination -
while holding, down, CTRL+SHIFT press ENTER instead of just pressing ENTER.
You will be able to identify an Array Formula, in the Formula Bar, if it is
confined in a pair of curly braces.]
Micky


"tran1728" wrote:

Dear All, pls help me.
I want to make a Vlookup, the data of Product est very big, Column A is
Item, Column B is Description, there have 3 Sheets for product ( Product01,
Product02 and Product03 ) .

I know just to do for Product01.
=VLOOKUP(A1,Product01!A:B,2,FALSE)

Pls help me the formula also for Product01!A:B, Product02!A:B and
Product03!A:B

Thanks so much.

Thanks ,Ms-Exl-Learner
Your formula is well, but i need display only the description, no the
commentaire.

Is possible ?

"Ms-Exl-Learner" wrote:

May be this…

If you want to combine Vlookup for all the three sheets in one formula then
try the below:-

=IF(A1="","",IF(ISNA(VLOOKUP(A1,Product01!A:B,2,FA LSE)),"["&UPPER(A1)&" Not
Available In Product01
Sheet]",IF(VLOOKUP(A1,Product01!A:B,2,FALSE)=0,"[Description is blank for
"&UPPER(A1)&" In Product01 Sheet]","[Product01 :
"&VLOOKUP(A1,Product01!A:B,2,FALSE))&"]")&CHAR(10)&IF(ISNA(VLOOKUP(A1,Product02!A:B,2,FAL SE)),"["&UPPER(A1)&"
Not Available In Product02
Sheet]",IF(VLOOKUP(A1,Product02!A:B,2,FALSE)=0,"[Description is blank for
"&UPPER(A1)&" In Product02 Sheet]","[Product02 :
"&VLOOKUP(A1,Product02!A:B,2,FALSE))&"]")&CHAR(10)&IF(ISNA(VLOOKUP(A1,Product03!A:B,2,FAL SE)),"["&UPPER(A1)&"
Not Available In Product03
Sheet]",IF(VLOOKUP(A1,Product03!A:B,2,FALSE)=0,"[Description is blank for
"&UPPER(A1)&" In Product03 Sheet]","[Product03 :
"&VLOOKUP(A1,Product03!A:B,2,FALSE)&"]")))

If you want the individual Vlookup formula then try the below:
Product 01:-
=VLOOKUP(A1,Product01!A:B,2,FALSE)

Product 02:-
=VLOOKUP(A1,Product02!A:B,2,FALSE)

Product 03:-
=VLOOKUP(A1,Product03!A:B,2,FALSE)
--
Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"tran1728" wrote:

Dear All, pls help me.
I want to make a Vlookup, the data of Product est very big, Column A is
Item, Column B is Description, there have 3 Sheets for product ( Product01,
Product02 and Product03 ) .

I know just to do for Product01.
=VLOOKUP(A1,Product01!A:B,2,FALSE)

Pls help me the formula also for Product01!A:B, Product02!A:B and
Product03!A:B

Thanks so much.

Try this.

Somewhere on the worksheet list your three lookup worksheets Product01,
Product02, Product03. Select the three cells and in the name box name them
MySheets. They will now be included in the formula.

Enter the formula where you want the answer to be using Ctrl + Shift +
Enter. You will get { } around the formula. This called Array-Enter. If
you change the formula you will need to array-enter again.

Enter the lookup value in A2 on the formula sheet.

A2:A200 is the lookup table on each of the Product sheets, adjust in the
formula to suit the data on the sheets, but all must be the same on each
sheet.

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

So, it looks on each sheet for whatever is in A2 and returns column B.

HTH
Regards,
Howard

"tran1728" wrote in message
...
Dear All, pls help me.
I want to make a Vlookup, the data of Product est very big, Column A is
Item, Column B is Description, there have 3 Sheets for product (
Product01,
Product02 and Product03 ) .

I know just to do for Product01.
=VLOOKUP(A1,Product01!A:B,2,FALSE)

Pls help me the formula also for Product01!A:B, Product02!A:B and
Product03!A:B

Thanks so much.

If the comments are Texts then use the below formula:-

=IF(A1="","",IF(ISNA(VLOOKUP(A1,Product01!A:B,2,FA LSE)),"",IF(VLOOKUP(A1,Product01!A:B,2,FALSE)=0,"" ,"["&VLOOKUP(A1,Product01!A:B,2,FALSE)&"]
"))&IF(ISNA(VLOOKUP(A1,Product02!A:B,2,FALSE)),"", IF(VLOOKUP(A1,Product02!A:B,2,FALSE)=0,"","["&VLOOKUP(A1,Product02!A:B,2,FALSE)&"]
"))&IF(ISNA(VLOOKUP(A1,Product03!A:B,2,FALSE)),"", IF(VLOOKUP(A1,Product03!A:B,2,FALSE)=0,"","["&VLOOKUP(A1,Product03!A:B,2,FALSE)&"] ")))

If the comments are Numbers and you would like to add it, then use the below
formula:-

=IF(A1="","",IF(ISNA(VLOOKUP(A1,Product01!A:B,2,FA LSE)),"",IF(VLOOKUP(A1,Product01!A:B,2,FALSE)=0,"" ,VLOOKUP(A1,Product01!A:B,2,FALSE)))+IF(ISNA(VLOOK UP(A1,Product02!A:B,2,FALSE)),"",IF(VLOOKUP(A1,Pro duct02!A:B,2,FALSE)=0,"",VLOOKUP(A1,Product02!A:B, 2,FALSE)))+IF(ISNA(VLOOKUP(A1,Product03!A:B,2,FALS E)),"",IF(VLOOKUP(A1,Product03!A:B,2,FALSE)=0,"",V LOOKUP(A1,Product03!A:B,2,FALSE))))

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"tran1728" wrote:

Thanks ,Ms-Exl-Learner
Your formula is well, but i need display only the description, no the
commentaire.

Is possible ?

"Ms-Exl-Learner" wrote:

May be this…

If you want to combine Vlookup for all the three sheets in one formula then
try the below:-

=IF(A1="","",IF(ISNA(VLOOKUP(A1,Product01!A:B,2,FA LSE)),"["&UPPER(A1)&" Not
Available In Product01
Sheet]",IF(VLOOKUP(A1,Product01!A:B,2,FALSE)=0,"[Description is blank for
"&UPPER(A1)&" In Product01 Sheet]","[Product01 :
"&VLOOKUP(A1,Product01!A:B,2,FALSE))&"]")&CHAR(10)&IF(ISNA(VLOOKUP(A1,Product02!A:B,2,FAL SE)),"["&UPPER(A1)&"
Not Available In Product02
Sheet]",IF(VLOOKUP(A1,Product02!A:B,2,FALSE)=0,"[Description is blank for
"&UPPER(A1)&" In Product02 Sheet]","[Product02 :
"&VLOOKUP(A1,Product02!A:B,2,FALSE))&"]")&CHAR(10)&IF(ISNA(VLOOKUP(A1,Product03!A:B,2,FAL SE)),"["&UPPER(A1)&"
Not Available In Product03
Sheet]",IF(VLOOKUP(A1,Product03!A:B,2,FALSE)=0,"[Description is blank for
"&UPPER(A1)&" In Product03 Sheet]","[Product03 :
"&VLOOKUP(A1,Product03!A:B,2,FALSE)&"]")))

If you want the individual Vlookup formula then try the below:
Product 01:-
=VLOOKUP(A1,Product01!A:B,2,FALSE)

Product 02:-
=VLOOKUP(A1,Product02!A:B,2,FALSE)

Product 03:-
=VLOOKUP(A1,Product03!A:B,2,FALSE)
--
Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"tran1728" wrote:

Dear All, pls help me.
I want to make a Vlookup, the data of Product est very big, Column A is
Item, Column B is Description, there have 3 Sheets for product ( Product01,
Product02 and Product03 ) .

I know just to do for Product01.
=VLOOKUP(A1,Product01!A:B,2,FALSE)

Pls help me the formula also for Product01!A:B, Product02!A:B and
Product03!A:B

Thanks so much.