#N/A

[300m]

=IF(LOOKUP(SMALL(Result!$I$2:$I$552,ROW()1),Result!$I$2:$I$552,Result!$B$2:$B$552)=A$1,LOOKUP(SMALL(Result!$I$2:$I$552,ROW()1),Result!$I$2:$I$552,Result!$D$2:$D$552),LOOKUP(SMALL(Result!$I$2:$I$552,ROW()1),Result!$I$2:$I$552,Result!$E$2:$E$552))

Hi,

I am having trouble with this formula. I get a #N/A error. Is there something wrong with above formula? I tried many variations but I keep getting error values.

Any help much appreciated.

 

[300m]

=IFERROR(IF(OR(B:B=I$1,C:C=I$1),DATEDIF(A:A,TODAY(),"D"),9999),999)

Entered in cell "I2".

 

[Aladin Akyurek]

=IFERROR(IF(OR(B:B=I$1,C:C=I$1),DATEDIF(A:A,TODAY(),"D"),9999),999)

Entered in cell "I2".

I meant the LOOKUP formula.

By the way, why do you need those two constants: 9999 and 999?

 

[300m]

I meant the LOOKUP formula.

By the way, why do you need those two constants: 9999 and 999?

Sorry. Ok I enter that formula in another sheet in cell A2. Looks like below. Sorry I don't know about those constants, but formula works real good.

=IF(LOOKUP(SMALL(Result!$I:$I,ROW()-1),Result!$I:$I,Result!$B:$B)=A$1,LOOKUP(SMALL(Result!$I:$I,ROW()-1),Result!$I:$I,Result!$F:$F),LOOKUP(SMALL(Result!$I:$I,ROW()-1),Result!$I:$I,Result!$G:$G))

Is there no way I can get away from the sorting of column "I"?. I cannot enter results when the workbook is so messed up

 

[Aladin Akyurek]

Sorry. Ok I enter that formula in another sheet in cell A2. Looks like below. Sorry I don't know about those constants, but formula works real good.

I would think that

I2, copied down:

=IFERROR(IF(OR(B:B=I$1,C:C=I$1),DATEDIF(A:A,TODAY(),"D"),""),"")

=IFERROR(IF(OR(B2=I$1,C2=I$1),DATEDIF(A2,TODAY(),"D"),""),"")

would also work.

=IF(LOOKUP(SMALL(Result!$I:$I,ROW()-1),Result!$I:$I,Result!$B:$B)=A$1,LOOKUP(SMALL(Result!$I:$I,ROW()-1),Result!$I:$I,Result!$F:$F),LOOKUP(SMALL(Result!$I:$I,ROW()-1),Result!$I:$I,Result!$G:$G))

Is there no way I can get away from the sorting of column "I"?. I cannot enter results when the workbook is so messed up

In A2 enter and copy down:

=IFERROR(IF(INDEX(Result!$B:$B,MATCH(SMALL(Result!$I:$I,ROWS($A$2:A2))=A$1,INDEX(Result!$F:$F,MATCH(SMALL(Result!$I:$I,ROWS($A$2:A2)),INDEX(Result!$G:$G,MATCH(SMALL(Result!$I:$I,ROWS($A$2:A2)),"")

which is meant to implement (although not sure about the intended working)

=IF(LOOKUP(SMALL(Result!$I:$I,ROW()-1),Result!$I:$I,Result!$B:$B)=A$1,LOOKUP(SMALL(Result!$I:$I,ROW()-1),Result!$I:$I,Result!$F:$F),LOOKUP(SMALL(Result!$I:$I,ROW()-1),Result!$I:$I,Result!$G:$G))

for unsorted match-reference.

Hope this helps.

 

[300m]

Aladin,

The second formula you gave me below is giving me an error? Are there maybe parentheses missing? There is also a space between R and O in ROWS, should I remove that space?

In A2 enter and copy down:

=IFERROR(IF(INDEX(Result!$B:$B,MATCH(SMALL(Result!$I:$I,ROWS($A$2:A2))=A$1,INDEX(Result!$F:$F,MATCH(SMALL(Result!$I:$I,R OWS($A$2:A2)),INDEX(Result!$G:$G,MATCH(SMALL(Result!$I:$I,ROWS($A$2:A2)),"")

 

[Aladin Akyurek]

Aladin,

The second formula you gave me below is giving me an error? Are there maybe parentheses missing? There is also a space between R and O in ROWS, should I remove that space?

In A2 enter and copy down:

=IFERROR(IF(INDEX(Result!$B:$B,MATCH(SMALL(Result!$I:$I,ROWS($A$2:A2))=A$1,INDEX(Result!$F:$F,MATCH(SMALL(Result!$I:$I,R OWS($A$2:A2)),INDEX(Result!$G:$G,MATCH(SMALL(Result!$I:$I,ROWS($A$2:A2)),"")

There should be no space. The Match bits are also incomplete.

=IFERROR(IF(INDEX(Result!$B:$B,MATCH(SMALL(Result!$I:$I,ROWS($A$2:A2)),Result!$I:$I,0))=A$1,
     INDEX(Result!$B:$B,MATCH(SMALL(Result!$I:$I,ROWS($A$2:A2)),Result!$I:$I,0)),
     INDEX(Result!$G:$G,MATCH(SMALL(Result!$I:$I,ROWS($A$2:A2)),Result!$I:$I,0))),"")

 

[300m]

Aladin,

Your formulas perform exactly as I wanted, really nice.
Thank you so much for your time and effort!

I would think that

I2, copied down:

=IFERROR(IF(OR(B:B=I$1,C:C=I$1),DATEDIF(A:A,TODAY(),"D"),""),"")

=IFERROR(IF(OR(B2=I$1,C2=I$1),DATEDIF(A2,TODAY(),"D"),""),"")

would also work.

The above 2 formulas both works. Which one do I choose, does it matter, is one better than the other?
Thank you so much! You saved me a lot of headache.

 

[Aladin Akyurek]

Aladin,

Your formulas perform exactly as I wanted, really nice.
Thank you so much for your time and effort!

That's great. Thanks for providing feedback.

I would think that
 
I2, copied down:
 
=IFERROR(IF(OR(B:B=I$1,C:C=I$1),DATEDIF(A:A,TODAY(),"D"),""),"")
 
=IFERROR(IF(OR(B2=I$1,C2=I$1),DATEDIF(A2,TODAY(),"D"),""),"")
 
would also work.

The above 2 formulas both works. Which one do I choose, does it matter, is one better than the other?
Thank you so much! You saved me a lot of headache.

I'd opt myself for the latter for it shows per record what is addressed.

 

[300m]

OK. Good to know.
Thank you!

 

[Aladin Akyurek]

OK. Good to know.
Thank you!

You are welcome.