#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.

 

[Aladin Akyurek]

Care to explain in words what this formula is intended to do?

 

[jim may]

What are you referring to with:

ROW()1

 

[300m]

Care to explain in words what this formula is intended to do?

I should have explained more but wanted first to know if there is a visible error with the formula. I had this working in another spreadsheet program and now trying to get it to function in Excel. I didn't make the formula myself, I got help.

It is intended to collect and sum two teams' goals count for each of the five most recent games played.

 

[300m]

What are you referring to with:

ROW()1

Jim, Sorry I can't really answer this. But maybe I should add that there is one help column for this that has the below formula:

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

 

[Aladin Akyurek]

I should have explained more but wanted first to know if there is a visible error with the formula. I had this working in another spreadsheet program and now trying to get it to function in Excel. I didn't make the formula myself, I got help.

It is intended to collect and sum two teams' goals count for each of the five most recent games played.

Jim posted ROW()1 which looks like a typo...

Is Result!$I$2:$I$552 sorted in ascending order? LOOKUP would require that...

Care to post the formula directly as is from its source?

 

[300m]

Jim posted ROW()1 which looks like a typo...

Is Result!$I$2:$I$552 sorted in ascending order? LOOKUP would require that...

Care to post the formula directly as is from its source?

No, the "I" column is not sorted in ascending order. I try it now and it really messes up my sheet but the #N/A error disapears. Instead insane number value.

The original formula was for mac and iWorks Numbers and it looked like this. It worked perfectly in Numbers, but Numbers is so slow, so I wanted to give it a go in Excel. Can I not use ROW() like this in Excel?

=IF(LOOKUP(SMALL(Main :: $F,ROW()-1),Main :: $F,Main :: $B)=A$1,LOOKUP(SMALL(Main :: $F,ROW()-1),Main :: $F,Main :: $D),LOOKUP(SMALL(Main :: $F,ROW()-1),Main :: $F,Main :: $E))

Thanks for helping me!

 

[Aladin Akyurek]

No, the "I" column is not sorted in ascending order. I try it now and it really messes up my sheet but the #N/A error disapears. Instead insane number value.

The original formula was for mac and iWorks Numbers and it looked like this. It worked perfectly in Numbers, but Numbers is so slow, so I wanted to give it a go in Excel. Can I not use ROW() like this in Excel?

=IF(LOOKUP(SMALL(Main :: $F,ROW()-1),Main :: $F,Main :: $B)=A$1,LOOKUP(SMALL(Main :: $F,ROW()-1),Main :: $F,Main :: $D),LOOKUP(SMALL(Main :: $F,ROW()-1),Main :: $F,Main :: $E))

Thanks for helping me!

I for one am unfamiliar with this syntax...

ROW() and ROW()-1 are admissible in Excel.

If the current row is 1, you'd get {0} with ROW()-1.

 

[300m]

Aladin,

After sorting "I" column in ascending order I now get the correct result . Thank you!
Only problem now it really messes up the workbook. Column "I" is even hidden.

Anyway, thank you for solving this.

 

[Aladin Akyurek]

Aladin,

After sorting "I" column in ascending order I now get the correct result . Thank you!
Only problem now it really messes up the workbook. Column "I" is even hidden.

Anyway, thank you for solving this.

That's great. Would you post the formula as you have it in Excel and in which cell you enter the first instance of it?