|
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 |
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 |
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 |
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 |
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 |
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 |
LOOKUP or VLOOKUP
hi again Michelle,
You might need to increase the range of your data selection. I mean here you have A1 till A3, I guess you should increase it from A3 to your requirement. Hope this works for you, as the formula you have written is just perfect. Otherwise do send me the excel file over my email address. It is shaildeogam at gmail.com Thanks again for the feedback. Shail MichelleS wrote: 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 |
LOOKUP or VLOOKUP
OK, I did it, still the same output.......OK, I promise this is the last
question...Shail, my data is not all in ascending order. I forgot with VLOOKUP, does all of your data have to be in ascending order ?? That may be my problem, but I cannot change the order, it will change my dollars. So, is there a brute force or cheat way to overlook the non-ascending data? "shail" wrote: hi again Michelle, You might need to increase the range of your data selection. I mean here you have A1 till A3, I guess you should increase it from A3 to your requirement. Hope this works for you, as the formula you have written is just perfect. Otherwise do send me the excel file over my email address. It is shaildeogam at gmail.com Thanks again for the feedback. Shail MichelleS wrote: 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),"")))))) Please HELP !! VeryVeryVery Much Appreciative, Michelle (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 |
LOOKUP or VLOOKUP
No Michelle, it is not necessary to have your data to be ascending,
descending or random. VLOOKUP will find its match, no matter how your data is. If you could have sent me the excel file I might have tried to fix your problem. And do feel free to ask questions anytime, I feel nice to help with the knowledege I have gained so far. I too am in a learning phase, I too keep asking the questions in this forum and get the answers. Thanks again. Shail MichelleS wrote: OK, I did it, still the same output.......OK, I promise this is the last question...Shail, my data is not all in ascending order. I forgot with VLOOKUP, does all of your data have to be in ascending order ?? That may be my problem, but I cannot change the order, it will change my dollars. So, is there a brute force or cheat way to overlook the non-ascending data? "shail" wrote: hi again Michelle, You might need to increase the range of your data selection. I mean here you have A1 till A3, I guess you should increase it from A3 to your requirement. Hope this works for you, as the formula you have written is just perfect. Otherwise do send me the excel file over my email address. It is shaildeogam at gmail.com Thanks again for the feedback. Shail MichelleS wrote: 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),"")))))) Please HELP !! VeryVeryVery Much Appreciative, Michelle (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 |
LOOKUP or VLOOKUP
Hi Again Shail,
I forwarded you an email yesterday at your e-address ), titled 'One of Your Microsoft Office Online Students'. I hope you receive it, if not please let me know. Thank you a bunch, Michelle "shail" wrote: No Michelle, it is not necessary to have your data to be ascending, descending or random. VLOOKUP will find its match, no matter how your data is. If you could have sent me the excel file I might have tried to fix your problem. And do feel free to ask questions anytime, I feel nice to help with the knowledege I have gained so far. I too am in a learning phase, I too keep asking the questions in this forum and get the answers. Thanks again. Shail MichelleS wrote: OK, I did it, still the same output.......OK, I promise this is the last question...Shail, my data is not all in ascending order. I forgot with VLOOKUP, does all of your data have to be in ascending order ?? That may be my problem, but I cannot change the order, it will change my dollars. So, is there a brute force or cheat way to overlook the non-ascending data? "shail" wrote: hi again Michelle, You might need to increase the range of your data selection. I mean here you have A1 till A3, I guess you should increase it from A3 to your requirement. Hope this works for you, as the formula you have written is just perfect. Otherwise do send me the excel file over my email address. It is shaildeogam at gmail.com Thanks again for the feedback. Shail MichelleS wrote: 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),"")))))) Please HELP !! VeryVeryVery Much Appreciative, Michelle (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 |
| All times are GMT +1. The time now is 03:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com