CODEWITHSUNDEEP
excelexcel-formula
evv_gis
evv_gis

Reputation: 104

Formula result shows as 0, but evaluates to the correct value

I have data on one sheet "Individual Events" that I am referencing on another sheet "Scoreboard". I am creating a unique ascending list of name values from "Individual Events" on the "Scoreboard" sheet. My formula is returning 0 as the result, however when I evaluate the formula, I see the correct name value.

enter image description here

Here is the formula I am using to get the generate the sorted unique name list:

={IFERROR(INDEX('Individual Events'!$A$2:$A$501, MATCH(SMALL(IF(COUNTIF($A$2:A2,'Individual Events'!$A$2:$A$501)=0, COUNTIF('Individual Events'!$A$2:$A$501, "<"&'Individual Events'!$A$2:$A$501), ""), 1), COUNTIF('Individual Events'!$A$2:$A$501, "<"&'Individual Events'!$A$2:$A$501), 0)),"")}

Here is what the Function Arguments window shows as the value: Formula Arguments Window

I originally thought this was a Ctrl+Shift+Enter, but that doesn't change the values.

Upvotes: 0

Views: 656

Answers (1)

dwirony
dwirony

Reputation: 5450

This issue was the circular reference - try this formula:

={IFERROR(INDEX('Individual Events'!$A$2:$A$501, MATCH(SMALL(IF(COUNTIF($A$1:A1,'Individual Events'!$A$2:$A$501)=0, COUNTIF('Individual Events'!$A$2:$A$501, "<"&'Individual Events'!$A$2:$A$501), ""), 1), COUNTIF('Individual Events'!$A$2:$A$501, "<"&'Individual Events'!$A$2:$A$501), 0)),"")}

You don't want to start at A2, you need to start at A1 to avoid the circular reference.

EDIT: Rather, your formula still doesn't really work... I can populate a unique list with this formula just fine, though:

=INDEX('Individual Events'!$A$2:$A$501,MATCH(0,COUNTIF(Scoreboard!$A$1:A1,'Individual Events'!$A$2:$A$501),0))

Upvotes: 1

Related Questions