Find matching rows

[Oeysbrei]

Hi,

I have posted this earlier and thought I had solved it, but with some testing it turned out I dident.
I need a formula that finds matching rows in the exact order, and then return the value of the location.

I´ve attached a samplefile for you to have a look at that I think will explane the challenge better.

Any help is much appreciated

Regards
Oeysbrei

[Andrew-R]

Try this formula:

=MAX(INDEX(ROW($N$1:$N$1000)*(ROW($N$1:$N$1000)<>ROW())*($N$1:$N$1000=N1)*($O$1:$O$1000=O1)*($P$1:$P$1000=P1)*($Q$1:$Q$1000=Q1)*($R$1:$R$1000=R1)*($S$1:$S$1000=S1)*($T$1:$T$1000=T1)*($U$1:$U$1000=U1)*($V$1:$V$1000=V1)*($W$1:$W$1000=W1),0))

It will return 0 if there are no matching rows.

[Oeysbrei]

Thank you,

This workes perfect. But just for my own curiosity, is it possible maybe by use of fhe IF function to make the cells blank when no matching rows?

Regards
Oeysbrei

[Andrew-R]

The problem with using an IF statement is you'd have to run that long formula twice (once to test if it returned 0, and again to display the value if it did not).

However, this little cheat should work OK:

=IFERROR(VALUE(TEXT(MAX(INDEX(ROW($N$1:$N$1000)*(ROW($N$1:$N$1000)<>ROW())*($N$1:$N$1000=N1)*($O$1:$O$1000=O1)*($P$1:$P$1000=P1)*($Q$1:$Q$1000=Q1)*($R$1:$R$1000=R1)*($S$1:$S$1000=S1)*($T$1:$T$1000=T1)*($U$1:$U$1000=U1)*($V$1:$V$1000=V1)*($W$1:$W$1000=W1),0)),"#")),"")

[Oeysbrei]

Thank you very much,

Now I can smile the rest of the day
Do you know of any sites that explanes how to create a formula?

Oeysbrei

[Andrew-R]

I don't personally know of any sites, but if you start a new thread then I'm sure somebody will be able to help you out.