If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Vlookup for 3 sheets
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. |
#3
|
|||
|
|||
Vlookup for 3 sheets
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. |
#4
|
|||
|
|||
Vlookup for 3 sheets
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. |
#5
|
|||
|
|||
Vlookup for 3 sheets
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. |
#6
|
|||
|
|||
Vlookup for 3 sheets
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. |
#7
|
|||
|
|||
Vlookup for 3 sheets
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. |
Thread Tools | |
Display Modes | |
|
|