|
Formula to look up values
Hello,
I need help in excel to find a way to look up values in a Microsoft Excel Spreadsheet based on a chosen product code in another sheet in the same excel-file (to be used in calculating price for different product qualities). The input-sheet may look like this : A B C D E F G 2 Pricing Model 3 4 5 Legering / Ovn Product Code 6 AL604101 7 The product-code is written in Cell D6. In another sheet there is information saved regarding each product code (qantities of 10 elements which is included in the product, stored in column G). 1 B C D E F G H I J K 2 Stored values Results 3 AL0605CH AL 0605 CH A 0,66 A 0,00 4 AL0605CH AL 0605 CH B 0,16 B 5 AL0605CH AL 0605 CH C 0,17 C 6 AL0605CH AL 0605 CH D 0,26 D 7 AL0605CH AL 0605 CH E 0,55 E 8 AL0605CH AL 0605 CH F 0,15 F 9 AL0605CH AL 0605 CH G 0 G 10 AL0605CH AL 0605 CH H 0,03 H 11 AL0605CH AL 0605 CH I 0 I 12 AL0605CH AL 0605 CH J 0 J 13 AL0605CH AL 0605 CH K 0 K 14 AL0605CH AL 0605 CH L 0 L 15 AL0605CH AL 0605 CH M 0 M 16 AL604101 AL 6041 01 A 0,41 17 AL604101 AL 6041 01 B 0,175 18 AL604101 AL 6041 01 C 0 19 AL604101 AL 6041 01 D 0 20 AL604101 AL 6041 01 E 0,41 21 AL604101 AL 6041 01 F 0 22 AL604101 AL 6041 01 G 0 23 AL604101 AL 6041 01 H 0 24 AL604101 AL 6041 01 I 0 25 AL604101 AL 6041 01 J 0 26 AL604101 AL 6041 01 K 0 27 AL604101 AL 6041 01 L 0 My problem is to find a way to pick up the correct quantity for each product code. More detailed : If product code AL0605CH is chosen in the input sheet, the value in sheet K3 should be 0,66 = G3, K4 = 0 = G4, K5 = 0,17 = G5 etc. Correspondently, if product code AL604101 is chosen, then i want K3=0,41=G16, K4=0,175=G17 etc. The example is a simplification, there are about 400 product codes which each have a value for all 13 elements. Column B is just an extraction of columns C, D and E, i thought this way would make it easier to put up a formula to extract the values I need. My questions are : How can I make a formula/function that puts the correct values in column K (cells K3-K15) depending om the chosen product code ? I have tried se several alternatives, but have trouble both with the format (text and numbers combined) and that i need to look up many values for each product code..... Any help will be greatly appreciated. Best regards, Eirik |
Formula to look up values
Assume the second sheet with the data is named Sheet2.
In K3 put the formula =if($D$6="","",if(Row(A1)Countif(Sheet2!$A$1:$A$2 000,$D$6),"",Index(Sheet2! $G$1:$G$2000,Match($D$6!Sheet2!$A$1:$A$1000)+Row(A 1)-1,1))) Change the 2000 in each case to reflect the number of rows that contain data on Sheet2. now drag fill it down to K15. -- Regards, Tom Ogilvy then drag fill it down the column to K "Eirik Sævareid" wrote in message ... Hello, I need help in excel to find a way to look up values in a Microsoft Excel Spreadsheet based on a chosen product code in another sheet in the same excel-file (to be used in calculating price for different product qualities). The input-sheet may look like this : A B C D E F G 2 Pricing Model 3 4 5 Legering / Ovn Product Code 6 AL604101 7 The product-code is written in Cell D6. In another sheet there is information saved regarding each product code (qantities of 10 elements which is included in the product, stored in column G). 1 B C D E F G H I J K 2 Stored values Results 3 AL0605CH AL 0605 CH A 0,66 A 0,00 4 AL0605CH AL 0605 CH B 0,16 B 5 AL0605CH AL 0605 CH C 0,17 C 6 AL0605CH AL 0605 CH D 0,26 D 7 AL0605CH AL 0605 CH E 0,55 E 8 AL0605CH AL 0605 CH F 0,15 F 9 AL0605CH AL 0605 CH G 0 G 10 AL0605CH AL 0605 CH H 0,03 H 11 AL0605CH AL 0605 CH I 0 I 12 AL0605CH AL 0605 CH J 0 J 13 AL0605CH AL 0605 CH K 0 K 14 AL0605CH AL 0605 CH L 0 L 15 AL0605CH AL 0605 CH M 0 M 16 AL604101 AL 6041 01 A 0,41 17 AL604101 AL 6041 01 B 0,175 18 AL604101 AL 6041 01 C 0 19 AL604101 AL 6041 01 D 0 20 AL604101 AL 6041 01 E 0,41 21 AL604101 AL 6041 01 F 0 22 AL604101 AL 6041 01 G 0 23 AL604101 AL 6041 01 H 0 24 AL604101 AL 6041 01 I 0 25 AL604101 AL 6041 01 J 0 26 AL604101 AL 6041 01 K 0 27 AL604101 AL 6041 01 L 0 My problem is to find a way to pick up the correct quantity for each product code. More detailed : If product code AL0605CH is chosen in the input sheet, the value in sheet K3 should be 0,66 = G3, K4 = 0 = G4, K5 = 0,17 = G5 etc. Correspondently, if product code AL604101 is chosen, then i want K3=0,41=G16, K4=0,175=G17 etc. The example is a simplification, there are about 400 product codes which each have a value for all 13 elements. Column B is just an extraction of columns C, D and E, i thought this way would make it easier to put up a formula to extract the values I need. My questions are : How can I make a formula/function that puts the correct values in column K (cells K3-K15) depending om the chosen product code ? I have tried se several alternatives, but have trouble both with the format (text and numbers combined) and that i need to look up many values for each product code..... Any help will be greatly appreciated. Best regards, Eirik |
Formula to look up values
Thanks, I have sent a follow-up question to your msn-address.
Regards, Eirik "Tom Ogilvy" skrev i melding ... Assume the second sheet with the data is named Sheet2. In K3 put the formula =if($D$6="","",if(Row(A1)Countif(Sheet2!$A$1:$A$2 000,$D$6),"",Index(Sheet2! $G$1:$G$2000,Match($D$6!Sheet2!$A$1:$A$1000)+Row(A 1)-1,1))) Change the 2000 in each case to reflect the number of rows that contain data on Sheet2. now drag fill it down to K15. -- Regards, Tom Ogilvy then drag fill it down the column to K "Eirik Sævareid" wrote in message ... Hello, I need help in excel to find a way to look up values in a Microsoft Excel Spreadsheet based on a chosen product code in another sheet in the same excel-file (to be used in calculating price for different product qualities). The input-sheet may look like this : A B C D E F G 2 Pricing Model 3 4 5 Legering / Ovn Product Code 6 AL604101 7 The product-code is written in Cell D6. In another sheet there is information saved regarding each product code (qantities of 10 elements which is included in the product, stored in column G). 1 B C D E F G H I J K 2 Stored values Results 3 AL0605CH AL 0605 CH A 0,66 A 0,00 4 AL0605CH AL 0605 CH B 0,16 B 5 AL0605CH AL 0605 CH C 0,17 C 6 AL0605CH AL 0605 CH D 0,26 D 7 AL0605CH AL 0605 CH E 0,55 E 8 AL0605CH AL 0605 CH F 0,15 F 9 AL0605CH AL 0605 CH G 0 G 10 AL0605CH AL 0605 CH H 0,03 H 11 AL0605CH AL 0605 CH I 0 I 12 AL0605CH AL 0605 CH J 0 J 13 AL0605CH AL 0605 CH K 0 K 14 AL0605CH AL 0605 CH L 0 L 15 AL0605CH AL 0605 CH M 0 M 16 AL604101 AL 6041 01 A 0,41 17 AL604101 AL 6041 01 B 0,175 18 AL604101 AL 6041 01 C 0 19 AL604101 AL 6041 01 D 0 20 AL604101 AL 6041 01 E 0,41 21 AL604101 AL 6041 01 F 0 22 AL604101 AL 6041 01 G 0 23 AL604101 AL 6041 01 H 0 24 AL604101 AL 6041 01 I 0 25 AL604101 AL 6041 01 J 0 26 AL604101 AL 6041 01 K 0 27 AL604101 AL 6041 01 L 0 My problem is to find a way to pick up the correct quantity for each product code. More detailed : If product code AL0605CH is chosen in the input sheet, the value in sheet K3 should be 0,66 = G3, K4 = 0 = G4, K5 = 0,17 = G5 etc. Correspondently, if product code AL604101 is chosen, then i want K3=0,41=G16, K4=0,175=G17 etc. The example is a simplification, there are about 400 product codes which each have a value for all 13 elements. Column B is just an extraction of columns C, D and E, i thought this way would make it easier to put up a formula to extract the values I need. My questions are : How can I make a formula/function that puts the correct values in column K (cells K3-K15) depending om the chosen product code ? I have tried se several alternatives, but have trouble both with the format (text and numbers combined) and that i need to look up many values for each product code..... Any help will be greatly appreciated. Best regards, Eirik |
Formula to look up values
Responded.
-- Regards, Tom Ogilvy "Eirik Sævareid" wrote in message ... Thanks, I have sent a follow-up question to your msn-address. Regards, Eirik "Tom Ogilvy" skrev i melding ... Assume the second sheet with the data is named Sheet2. In K3 put the formula =if($D$6="","",if(Row(A1)Countif(Sheet2!$A$1:$A$2 000,$D$6),"",Index(Sheet2! $G$1:$G$2000,Match($D$6!Sheet2!$A$1:$A$1000)+Row(A 1)-1,1))) Change the 2000 in each case to reflect the number of rows that contain data on Sheet2. now drag fill it down to K15. -- Regards, Tom Ogilvy then drag fill it down the column to K "Eirik Sævareid" wrote in message ... Hello, I need help in excel to find a way to look up values in a Microsoft Excel Spreadsheet based on a chosen product code in another sheet in the same excel-file (to be used in calculating price for different product qualities). The input-sheet may look like this : A B C D E F G 2 Pricing Model 3 4 5 Legering / Ovn Product Code 6 AL604101 7 The product-code is written in Cell D6. In another sheet there is information saved regarding each product code (qantities of 10 elements which is included in the product, stored in column G). 1 B C D E F G H I J K 2 Stored values Results 3 AL0605CH AL 0605 CH A 0,66 A 0,00 4 AL0605CH AL 0605 CH B 0,16 B 5 AL0605CH AL 0605 CH C 0,17 C 6 AL0605CH AL 0605 CH D 0,26 D 7 AL0605CH AL 0605 CH E 0,55 E 8 AL0605CH AL 0605 CH F 0,15 F 9 AL0605CH AL 0605 CH G 0 G 10 AL0605CH AL 0605 CH H 0,03 H 11 AL0605CH AL 0605 CH I 0 I 12 AL0605CH AL 0605 CH J 0 J 13 AL0605CH AL 0605 CH K 0 K 14 AL0605CH AL 0605 CH L 0 L 15 AL0605CH AL 0605 CH M 0 M 16 AL604101 AL 6041 01 A 0,41 17 AL604101 AL 6041 01 B 0,175 18 AL604101 AL 6041 01 C 0 19 AL604101 AL 6041 01 D 0 20 AL604101 AL 6041 01 E 0,41 21 AL604101 AL 6041 01 F 0 22 AL604101 AL 6041 01 G 0 23 AL604101 AL 6041 01 H 0 24 AL604101 AL 6041 01 I 0 25 AL604101 AL 6041 01 J 0 26 AL604101 AL 6041 01 K 0 27 AL604101 AL 6041 01 L 0 My problem is to find a way to pick up the correct quantity for each product code. More detailed : If product code AL0605CH is chosen in the input sheet, the value in sheet K3 should be 0,66 = G3, K4 = 0 = G4, K5 = 0,17 = G5 etc. Correspondently, if product code AL604101 is chosen, then i want K3=0,41=G16, K4=0,175=G17 etc. The example is a simplification, there are about 400 product codes which each have a value for all 13 elements. Column B is just an extraction of columns C, D and E, i thought this way would make it easier to put up a formula to extract the values I need. My questions are : How can I make a formula/function that puts the correct values in column K (cells K3-K15) depending om the chosen product code ? I have tried se several alternatives, but have trouble both with the format (text and numbers combined) and that i need to look up many values for each product code..... Any help will be greatly appreciated. Best regards, Eirik |
| All times are GMT +1. The time now is 09:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com