HELP on INDEX/MATCH multiple ocurence match values needed ..DRIVING ME CRAZY!!!

[buddy1000]

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.

 

[buddy1000]

I spoke a bit too soon Aladin but it DOESN NOT work for the multiple occurence.
AA125458 and BB548790 occure three times and DE125456 two times.

For both AA125458 and BB548790 (occures twice in 1000200 and once in 1000120) the formula gives for 1000200 twice the first value.


It seems that it distinguishes between 1000200 and 1000120 but not between values that occure twice in 1000200 or twice in 1000120.
This seems very strange since the distinct is made through SAAB and SAAB-2.

 

[Aladin Akyurek]

I spoke a bit too soon Aladin but it DOESN NOT work for the multiple occurence.
AA125458 and BB548790 occure three times and DE125456 two times.

For both AA125458 and BB548790 (occures twice in 1000200 and once in 1000120) the formula gives for 1000200 twice the first value.


It seems that it distinguishes between 1000200 and 1000120 but not between values that occure twice in 1000200 or twice in 1000120.
This seems very strange since the distinct is made through SAAB and SAAB-2.

{"BASS";"SABB";"SABB-2";"SABB-3";"etc"}

is wrong. The values must be in ascending order:

{"BASS";"etc","SABB";"SABB-2";"SABB-3"}

By the way, if the two lookup values do not contrue a unique key, the idea of comparison becomes meaningless...