Matching and Index Functions

[ggmac]

Hi guys, my first time on this site, hope everyone is well. I started paying around with the match and index function on excel, and i almost got it to do what i want. I have a small issue, hoping you could help me out with it. This is what i have so far...

[TABLE="width: 514"]
<tbody>[TR]
[TD="width: 64"]set 1[/TD]
[TD="width: 64"]#1[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]set 2[/TD]
[TD="width: 64"]#2[/TD]
[TD="class: xl63, width: 64"]where[/TD]
[TD="class: xl63, width: 130"]value coresponding[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl64, align: right"]1[/TD]
[TD="class: xl64, align: right"]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]-5[/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]3[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]-8[/TD]
[TD="class: xl64, align: right"]2[/TD]
[TD="class: xl64, align: right"]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]-8[/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]-5[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]C[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl64, align: right"]5[/TD]
[TD="class: xl64, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
The formulas I used are as follows:
under where...
=MATCH($N4,$Q$4:$Q$8,0), dragged all the way down. The only problem i have is the following.
Is you look at set 1 A and I search for it in set 2 it keeps bringing up that A is in row 1. For the first A it is on row 1, which is correct, but the second A is on row 4, how can i get it to to show 4 rather than 1. Any input please.

Thanks

 

[benishiryo]

hi ggmac. not sure which column is referring to which. assuming you are trying to match set 1 (column N) to Set 2 (column Q), then try:
=SMALL(INDEX(($Q$2:$Q$6=N2)*(ROW($Q$2:$Q$6)-ROW($Q$2)+1),),COUNTIF($N$2:$N$6,"<>"&N2)+COUNTIF(N$2:N2,N2))

so the answers should be 1, 4, 2, 3, 5?

 

[ggmac]

hi ggmac. not sure which column is referring to which. assuming you are trying to match set 1 (column N) to Set 2 (column Q), then try:
=SMALL(INDEX(($Q$2:$Q$6=N2)*(ROW($Q$2:$Q$6)-ROW($Q$2)+1),),COUNTIF($N$2:$N$6,"<>"&N2)+COUNTIF(N$2:N2,N2))

so the answers should be 1, 4, 2, 3, 5?

Hi Benishiryo, thanks for the code, ill work on this a bit more. ya the answer should be 1, 4, 2, 3, 5 under where. Since this is off it throws my index function also, but i think once the answers under function are right that should correct itself. Ill let you know how it goes thanks.