Vlookup for 3 sheets

  • Thread starter Thread starter tran1728
  • Start date Start date
T

tran1728

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,FALSE)),"["&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,FALSE)),"["&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,FALSE)),"["&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)
 
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


מיכ×ל (מיקי) ×בידן said:
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 are:
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 said:
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.
Click to expand...
Click to expand...
 
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 ?




מיכ×ל (מיקי) ×בידן said:
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 said:
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


מיכ×ל (מיקי) ×בידן said:
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 are:
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


:

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.
Click to expand...
Click to expand...
Click to expand...
 
Thanks ,Ms-Exl-Learner
Your formula is well, but i need display only the description, no the
commentaire.

Is possible ?

Ms-Exl-Learner said:
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,FALSE)),"["&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,FALSE)),"["&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,FALSE)),"["&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 said:
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.
Click to expand...
Click to expand...
 
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
 
If the comments are Texts then use the below formula:-

=IF(A1="","",IF(ISNA(VLOOKUP(A1,Product01!A:B,2,FALSE)),"",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,FALSE)),"",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))))

Remember to Click Yes, if this post helps!

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


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

Is possible ?

Ms-Exl-Learner said:
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,FALSE)),"["&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,FALSE)),"["&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,FALSE)),"["&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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top