| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP or VLOOKUP
I need help with LOOKUP or VLOOKUP.
I have two EXCEL 2000 documents ( Estimates.xls and Prices.xls). (this is a snapshot of ESTIMATES.xls) A B C 1 2 Product Description Est Price 3 400 _____ $____ 4 600 _____ $____ 5 900 _____ $____ (this is a snapshot of PRICES.xls, Tab or Sheet 1) A B C 1 Product Description Price 2 400 Red XYZ $47.00 3 500 Blue SPQ $68.00 (this is a snapshot of PRICES.xls, Tab or Sheet 2) A B C 1 Product Description Price 2 600 Green XYZ $40.00 3 800 Brown SPQ $88.00 (this is a snapshot of PRICES.xls, Tab or Sheet 3) A B C 1 Product Description Price 2 900 Black XYZ $70.00 3 950 White SPQ $38.00 I would like to type a product code into cell A3 on ESTIMATES.xls and have it automatically populate cells B3 and C3 with info from the PRICE.xls. The PRICE.xls has 3 tabs or sheets that need to be searched in order to populate correctly. Any help with this is appreciated. (ps: I'm a beginner so please use details in wording) Michelle |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
LOOKUP or VLOOKUP
Hi Michelle,
Enter the formula at B3 as below =IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!A$1:B$3,2,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!A$1:B$3,2,FALSE)) Enter this pressing keys <CTRL<SHIFT<ENTER and braces {} will appear around the formula. Copy down till B5. And for next column that is C, copy the formula and make changes as below: =IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!A$1:B$3,3,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!A$1:B$3,3,FALSE)) I hope this works for you. Do send me the feedback to help you over this if any error occur. For now, do it for just two sheets. If this is working for you I will send you the formula for three sheet (tabs) Thanks, Shail MichelleS wrote: I need help with LOOKUP or VLOOKUP. I have two EXCEL 2000 documents ( Estimates.xls and Prices.xls). (this is a snapshot of ESTIMATES.xls) A B C 1 2 Product Description Est Price 3 400 _____ $____ 4 600 _____ $____ 5 900 _____ $____ (this is a snapshot of PRICES.xls, Tab or Sheet 1) A B C 1 Product Description Price 2 400 Red XYZ $47.00 3 500 Blue SPQ $68.00 (this is a snapshot of PRICES.xls, Tab or Sheet 2) A B C 1 Product Description Price 2 600 Green XYZ $40.00 3 800 Brown SPQ $88.00 (this is a snapshot of PRICES.xls, Tab or Sheet 3) A B C 1 Product Description Price 2 900 Black XYZ $70.00 3 950 White SPQ $38.00 I would like to type a product code into cell A3 on ESTIMATES.xls and have it automatically populate cells B3 and C3 with info from the PRICE.xls. The PRICE.xls has 3 tabs or sheets that need to be searched in order to populate correctly. Any help with this is appreciated. (ps: I'm a beginner so please use details in wording) Michelle |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
LOOKUP or VLOOKUP
Use this formula for column 2 -
=IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!$A$1:$B*$3,2,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!$A$1:$B$3,2,FALSE)) and for column 3 - =IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!$A$1:$B*$3,3,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!$A$1:$B$3,3,FALSE)) Ignore "dash" ( - ), if they appear inside the formula. thanks again Shail shail wrote: Hi Michelle, Enter the formula at B3 as below =IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!A$1:B$3,2,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!A$1:B$3,2,FALSE)) Enter this pressing keys <CTRL<SHIFT<ENTER and braces {} will appear around the formula. Copy down till B5. And for next column that is C, copy the formula and make changes as below: =IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!A$1:B$3,3,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!A$1:B$3,3,FALSE)) I hope this works for you. Do send me the feedback to help you over this if any error occur. For now, do it for just two sheets. If this is working for you I will send you the formula for three sheet (tabs) Thanks, Shail MichelleS wrote: I need help with LOOKUP or VLOOKUP. I have two EXCEL 2000 documents ( Estimates.xls and Prices.xls). (this is a snapshot of ESTIMATES.xls) A B C 1 2 Product Description Est Price 3 400 _____ $____ 4 600 _____ $____ 5 900 _____ $____ (this is a snapshot of PRICES.xls, Tab or Sheet 1) A B C 1 Product Description Price 2 400 Red XYZ $47.00 3 500 Blue SPQ $68.00 (this is a snapshot of PRICES.xls, Tab or Sheet 2) A B C 1 Product Description Price 2 600 Green XYZ $40.00 3 800 Brown SPQ $88.00 (this is a snapshot of PRICES.xls, Tab or Sheet 3) A B C 1 Product Description Price 2 900 Black XYZ $70.00 3 950 White SPQ $38.00 I would like to type a product code into cell A3 on ESTIMATES.xls and have it automatically populate cells B3 and C3 with info from the PRICE.xls. The PRICE.xls has 3 tabs or sheets that need to be searched in order to populate correctly. Any help with this is appreciated. (ps: I'm a beginner so please use details in wording) Michelle |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
LOOKUP or VLOOKUP
SHAIL,
You are a life saver....THANK YOU !! I have 4 more tabs or sheets in that same document, should I continue the formula string to include those tabs? Example, you gave me the formula for the first 2 tabs, now would that same IF(OR........ formula work, if I add on 4 more VLOOKUPs?? "shail" wrote: Use this formula for column 2 - =IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!$A$1:$BĀ*$3,2,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!$A$1:$B$3,2,FALSE)) and for column 3 - =IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!$A$1:$BĀ*$3,3,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!$A$1:$B$3,3,FALSE)) Ignore "dash" ( - ), if they appear inside the formula. thanks again Shail shail wrote: Hi Michelle, Enter the formula at B3 as below =IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!A$1:B$3,2,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!A$1:B$3,2,FALSE)) Enter this pressing keys <CTRL<SHIFT<ENTER and braces {} will appear around the formula. Copy down till B5. And for next column that is C, copy the formula and make changes as below: =IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!A$1:B$3,3,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!A$1:B$3,3,FALSE)) I hope this works for you. Do send me the feedback to help you over this if any error occur. For now, do it for just two sheets. If this is working for you I will send you the formula for three sheet (tabs) Thanks, Shail MichelleS wrote: I need help with LOOKUP or VLOOKUP. I have two EXCEL 2000 documents ( Estimates.xls and Prices.xls). (this is a snapshot of ESTIMATES.xls) A B C 1 2 Product Description Est Price 3 400 _____ $____ 4 600 _____ $____ 5 900 _____ $____ (this is a snapshot of PRICES.xls, Tab or Sheet 1) A B C 1 Product Description Price 2 400 Red XYZ $47.00 3 500 Blue SPQ $68.00 (this is a snapshot of PRICES.xls, Tab or Sheet 2) A B C 1 Product Description Price 2 600 Green XYZ $40.00 3 800 Brown SPQ $88.00 (this is a snapshot of PRICES.xls, Tab or Sheet 3) A B C 1 Product Description Price 2 900 Black XYZ $70.00 3 950 White SPQ $38.00 I would like to type a product code into cell A3 on ESTIMATES.xls and have it automatically populate cells B3 and C3 with info from the PRICE.xls. The PRICE.xls has 3 tabs or sheets that need to be searched in order to populate correctly. Any help with this is appreciated. (ps: I'm a beginner so please use details in wording) Michelle |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
LOOKUP or VLOOKUP
Hi Michelle,
Thanks for the feedback. Yes, you can use IF & OR for next tabs. I have made this as below. =IF(OR([Prices.xls]sheet1!$A$1:$A$5=A1),VLOOKUP(A1,[Prices.xls]sheet1!$A$1:$B$5,2,FALSE),IF(OR([Prices.xls]sheet2!$A$1:$A$5=A1),VLOOKUP(A1,[Prices.xls]sheet2!$A$1:$B$5,2,FALSE),IF(OR([Prices.xls]sheet3!$A$1:$A$5=A1),VLOOKUP(A1,[Prices.xls]sheet3!$A$1:$B$5,2,FALSE),IF(OR([Prices.xls]sheet4!$A$1:$A$5=A1),VLOOKUP(A1,[Prices.xls]sheet4!$A$1:$B$5,2,FALSE),"")))) Hope that too work. Thanks, Shail MichelleS wrote: SHAIL, You are a life saver....THANK YOU !! I have 4 more tabs or sheets in that same document, should I continue the formula string to include those tabs? Example, you gave me the formula for the first 2 tabs, now would that same IF(OR........ formula work, if I add on 4 more VLOOKUPs?? "shail" wrote: Use this formula for column 2 - =IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!$A$1:$B*$3,2,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!$A$1:$B$3,2,FALSE)) and for column 3 - =IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!$A$1:$B*$3,3,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!$A$1:$B$3,3,FALSE)) Ignore "dash" ( - ), if they appear inside the formula. thanks again Shail shail wrote: Hi Michelle, Enter the formula at B3 as below =IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!A$1:B$3,2,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!A$1:B$3,2,FALSE)) Enter this pressing keys <CTRL<SHIFT<ENTER and braces {} will appear around the formula. Copy down till B5. And for next column that is C, copy the formula and make changes as below: =IF(OR([Prices.xls]sheet1!$A$1:$A$3=A3),VLOOKUP(A3,[Prices.xls]sheet1!A$1:B$3,3,FALSE),VLOOKUP(A3,[Prices.xls]sheet2!A$1:B$3,3,FALSE)) I hope this works for you. Do send me the feedback to help you over this if any error occur. For now, do it for just two sheets. If this is working for you I will send you the formula for three sheet (tabs) Thanks, Shail MichelleS wrote: I need help with LOOKUP or VLOOKUP. I have two EXCEL 2000 documents ( Estimates.xls and Prices.xls). (this is a snapshot of ESTIMATES.xls) A B C 1 2 Product Description Est Price 3 400 _____ $____ 4 600 _____ $____ 5 900 _____ $____ (this is a snapshot of PRICES.xls, Tab or Sheet 1) A B C 1 Product Description Price 2 400 Red XYZ $47.00 3 500 Blue SPQ $68.00 (this is a snapshot of PRICES.xls, Tab or Sheet 2) A B C 1 Product Description Price 2 600 Green XYZ $40.00 3 800 Brown SPQ $88.00 (this is a snapshot of PRICES.xls, Tab or Sheet 3) A B C 1 Product Description Price 2 900 Black XYZ $70.00 3 950 White SPQ $38.00 I would like to type a product code into cell A3 on ESTIMATES.xls and have it automatically populate cells B3 and C3 with info from the PRICE.xls. The PRICE.xls has 3 tabs or sheets that need to be searched in order to populate correctly. Any help with this is appreciated. (ps: I'm a beginner so please use details in wording) Michelle |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
LOOKUP or VLOOKUP
Shail,
You are SUPERB, THANKS A TRILLION, but.......... Your last formula only works for sheet1 and sheet2. It did not work for sheet3, sheet4, sheet5 and sheet6. Example: When I type a product code from sheet5 in cell A3 on ESTIMATE.xls, then the cell B3 comes back with an empty cell. But if I type a product code from sheet1 or sheet2 it works. See my formula below: IF(OR('[Prices.xls]sheet1'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet1'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet2'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet2'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet3'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet3'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet4'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet4'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet5'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet5'!$A$1:$B$3,2,FALSE),IF(OR('[Prices.xls]sheet6'!$A$1:$A$3=A3),VLOOKUP(A3,'[Prices.xls]sheet6'!$A$1:$B$3,2,FALSE),"")))))) I did hit Control,Shift,Enter at the same time after the last paranthese. Am I missing a comma, letter,quote, etc. ?? Please HELP !! VeryVeryVery Much Appreciative, Michelle "shail" wrote: Hi Michelle, Thanks for the feedback. Yes, you can use IF & OR for next tabs. I have made this as below. =IF(OR([Prices.xls]sheet1!$A$1:$A$5=A1),VLOOKUP(A1,[Prices.xls]sheet1!$A$1:$B$5,2,FALSE),IF(OR([Prices.xls]sheet2!$A$1:$A$5=A1),VLOOKUP(A1,[Prices.xls]sheet2!$A$1:$B$5,2,FALSE),IF(OR([Prices.xls]sheet3!$A$1:$A$5=A1),VLOOKUP(A1,[Prices.xls]sheet3!$A$1:$B$5,2,FALSE),IF(OR([Prices.xls]sheet4!$A$1:$A$5=A1),VLOOKUP(A1,[Prices.xls]sheet4!$A$1:$B$5,2,FALSE),"")))) Hope that too work. Thanks, Shail MichelleS wrote: SHAIL, You are a life saver....THANK YOU !! I have two EXCEL 2000 documents ( Estimates.xls and Prices.xls). (this is a snapshot of ESTIMATES.xls) A B C 1 2 Product Description Est Price 3 400 _____ $____ 4 600 _____ $____ 5 900 _____ $____ (this is a snapshot of PRICES.xls, Tab or Sheet 1) A B C 1 Product Description Price 2 400 Red XYZ $47.00 3 500 Blue SPQ $68.00 (this is a snapshot of PRICES.xls, Tab or Sheet 2) A B C 1 Product Description Price 2 600 Green XYZ $40.00 3 800 Brown SPQ $88.00 (this is a snapshot of PRICES.xls, Tab or Sheet 3) A B C 1 Product Description Price 2 900 Black XYZ $70.00 3 950 White SPQ $38.00 I would like to type a product code into cell A3 on ESTIMATES.xls and have it automatically populate cells B3 and C3 with info from the PRICE.xls. The PRICE.xls has 3 tabs or sheets that need to be searched in order to populate correctly. Any help with this is appreciated. (ps: I'm a beginner so please use details in wording) Michelle |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup. Match. Lookup. Wtf? | Excel Discussion (Misc queries) | |||
| Double lookup without using vlookup? | Excel Worksheet Functions | |||
| IF, VLOOKUP & LOOKUP TABLE ON OTHER SHEET | Excel Discussion (Misc queries) | |||
| Getting #N/A from Vlookup when matching value exist in the lookup data range. | Excel Worksheet Functions | |||
| VLOOKUP - dealing with lookup_value differences | Excel Worksheet Functions | |||
Hybrid Mode
