example.xls | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Ccode | Icode | nr. | |||
2 | 1000200 | AA125458 | 11000 | |||
3 | 1000200 | DE125456 | 16980 | |||
4 | 1000200 | BB548790 | 22960 | |||
5 | 1000200 | MM54875 | 28940 | |||
6 | 1000200 | PP124878 | 34920 | |||
7 | 1000200 | AA125458 | 40900 | |||
8 | 1000200 | BB548790 | 46880 | |||
9 | 1000200 | KL245542 | 52860 | |||
10 | 1000200 | DE125456 | 46880 | |||
11 | 1000120 | AA125458 | 52860 | |||
12 | 1000120 | TY784259 | 58840 | |||
13 | 1000120 | BB548790 | 64820 | |||
14 | 1000120 | MQ548787 | 70800 | |||
15 | ||||||
16 | ||||||
INPUT 1 |
example.xls | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Ccode | Icode | nr. | |||
2 | SABB | AA125458 | 11000 | |||
3 | SABB | DE125456 | 16980 | |||
4 | SABB | BB548790 | 22960 | |||
5 | SABB | MM54875 | 28940 | |||
6 | SABB | PP124878 | 34920 | |||
7 | SABB-2 | AA125458 | 40900 | |||
8 | SABB | BB548790 | 46880 | |||
9 | SABB | KL245542 | 52860 | |||
10 | SABB-2 | DE125456 | 46880 | |||
11 | BASS | AA125458 | 52860 | |||
12 | BASS | TY784259 | 58840 | |||
13 | BASS | BB548790 | 64820 | |||
14 | BASS | MQ548787 | 70800 | |||
INPUT 2 |
example.xls | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | INPUT 1 | INPUT 2 | CHECK | ||||||||||
2 | Ccode | Icode | nr. | Ccode | Icode | nr. | |||||||
3 | 1000200 | AA125458 | 11000 | FALSE | FALSE | ||||||||
4 | 1000200 | DE125456 | 16980 | FALSE | FALSE | ||||||||
5 | 1000200 | BB548790 | 22960 | FALSE | FALSE | ||||||||
6 | 1000200 | MM54875 | 28940 | FALSE | FALSE | ||||||||
7 | 1000200 | PP124878 | 34920 | FALSE | FALSE | ||||||||
8 | 1000200 | AA125458 | 11000 | FALSE | FALSE | ||||||||
9 | 1000200 | BB548790 | 22960 | FALSE | FALSE | ||||||||
10 | 1000200 | KL245542 | 52860 | FALSE | FALSE | ||||||||
11 | 1000200 | DE125456 | 16980 | FALSE | FALSE | ||||||||
12 | 1000120 | AA125458 | 52860 | FALSE | FALSE | ||||||||
13 | 1000120 | TY784259 | 58840 | FALSE | FALSE | ||||||||
14 | 1000120 | BB548790 | 64820 | ||||||||||
15 | 1000120 | MQ548787 | 70800 | ||||||||||
16 | |||||||||||||
17 | |||||||||||||
LOOKUP |
I am trying to lookup values with multiple occurence by using Index/match.
I have 3 sheets
Input sheet 1, Input sheet 2 and a Sheet I call Lookup.
In input sheet 1, I have a column Ccode, Icode and nr.
In input sheet 2 ,I have the same columns.
Icode and nr. sheets are SIMILAIR in both sheets.
The Ccode is has different parameters.
The Ccode in Input 1 can be eighter 1000200 or 1000120.
1000200 corresponds to SABB and SABB-2 AND 1000120to BASS
Column Ccode in Input 2 can be SABB, SABB-2 or BASS.
In the Lookup sheet, column C (nr. ) contains the formula
{=INDEX('INPUT 1'!$C$2:$C$65536,MATCH(A3,IF(LOOKUP!B$3:B$65536=B3,LOOKUP!A$3:A$65536),0))} which give sme the nr. corresponding to the Icode and Ccode of Input 1.
THE PROBLEM is using the formula to lookuo column H (nr) for Input 2.
If I use the same formula it does not work and disregards the multiple occurences.
Can anyone help me with a formula that will look at the Icode in columb B (Lookup Sheet), go to INPUT 2 and give me the corresponding nr.