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.

 

[venkat1926]

quote
that will look at the Icode in columb B (Lookup Sheet), go to INPUT 2 and give me the corresponding nr.
unquote

try this
in lookup sheet in cell C2 copy this formula
=INDEX(input2!$C:$C,MATCH(B2,input2!$B:$B,0),1)
you will get 11000
you can copy this formula down

 

[venkat1926]

I forgot to tell you that index--match function will find ONLY THE FIRST occurence. If you want to consider more occurences of same value and corresponding Nr value then you should have a macro
please clarify this in your next posting.

 

[buddy1000]

That is my point exactly....
The formula you gave me will give only the first occurence unless you match it.

This is the formula I use to find the values for Input 1. It works perfectly and NO VBA needed.

{=INDEX('INPUT 1'!$C$2:$C$65536,MATCH(A3,IF(LOOKUP!B$3:B$65536=B3,LOOKUP!A$3:A$65536),0))}

This formula does not work however with Input 2 due to complicated match values.

So let's say
I want in H14 in Lookup sheet the value that belongs to the second occurence of BB548790 matching BASS. Using the original formula will return 22960

!!!!!!So lookup BB548790 in Input 2 and return 64820. (second occurence in this case)!!!!!!

Any one has an idea on how to improve the formula to make it return that value instead of
22960.

 

[Aladin Akyurek]

Can you just disregard the formulas that you have and state clearly in words what you want to happen on the LOOKUP sheet? You need to specify the givens (lookup values) on the LOOKUP sheet...

 

[buddy1000]

Hello aladin...

In the lookup sheet (column H), I want Column B to be looked up, Go toInput sheet 2 and return the corresponding value. (column C)

 

[Aladin Akyurek]

Hello aladin...

In the lookup sheet (column H), I want Column B to be looked up, Go toInput sheet 2 and return the corresponding value. (column C)

What is the big idea? Comparing [ nr. ] of INPUT 1 with that of INPUT 2? If so, is Ccode relevant for the comparison?

 

[buddy1000]

Yes Aladin..comparing Input 1 with Input 2 and Ccode is relevant for comparison.
Only problem is that Input 1 uses different codes than Input 2.

!!note!!
1000200 in Input 1 corresponds with SAAB and SAAB-2 in input 2 and 1000120 with BASS
An Icode can have multiple occurence as seen.
ex. BB548790 has 3 occurences, (In Input 1 twice as 1000200 and once as 1000120) This is split up in Input 2 as SAAB-1, SAAB and as BASS!!

 

[Aladin Akyurek]

I don't think you need the LOOKUP sheet...

D2 on INPUT 1...

Control+shift+enter, not just enter...

=INDEX('INPUT 2'!$C$2:$C$14,
    MATCH(1,IF(LOOKUP('INPUT 2'!$A$2:$A$14,
         {"BASS";"SABB";"SABB-2"},{1000120;1000200;1000200})=A2,
                 IF('INPUT 2'!$B$2:$B$14=B2,1)),0))

...and copy down.

It's essential that column A on INPUT 2 does only house values from {"BASS";"SABB";"SABB-2"} and nothing else.

E2 on INPUT 1

=C2=D2

...and copy down.

Yes Aladin..comparing Input 1 with Input 2 and Ccode is relevant for comparison.
Only problem is that Input 1 uses different codes than Input 2.

!!note!!
1000200 in Input 1 corresponds with SAAB and SAAB-2 in input 2 and 1000120 with BASS
An Icode can have multiple occurence as seen.
ex. BB548790 has 3 occurences, (In Input 1 twice as 1000200 and once as 1000120) This is split up in Input 2 as SAAB-1, SAAB and as BASS!!

 

[buddy1000]

Thank you Aladin..It works.

Nice one since it also allows me to adjust it.

example. If SAAB-3 is added to the 1000200, I can make adjustments in the formula.

=INDEX('INPUT 2'!$C$2:$C$14,
MATCH(1,IF(LOOKUP('INPUT 2'!$A$2:$A$14,
{"BASS";"SABB";"SABB-2";"SABB-3";"etc"},1000120;1000200;1000200;1000200;etc})=A2,
IF('INPUT 2'!$B$2:$B$14=B2,1)),0))

Thank you....