Formula to look up values

[Eirik Sævareid]

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

 

[Tom Ogilvy]

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$2000,$D$6),"",Index(Sheet2!
$G$1:$G$2000,Match($D$6!Sheet2!$A$1:$A$1000)+Row(A1)-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]

Thanks, I have sent a follow-up question to your msn-address.

Regards,

Eirik

 

[Tom Ogilvy]

Responded.

--
Regards,
Tom Ogilvy

Thanks, I have sent a follow-up question to your msn-address.

Regards,

Eirik


=if($D$6="","",if(Row(A1)>Countif(Sheet2!$A$1:$A$2000,$D$6),"",Index(Sheet2! used