0

I have an unusual case with Excel and #REF! error. I've this formula:

=INDEX($N$621:$WO$621;SMALL($N626:$WO626;2);0)

which gives me sometimes a proper value and sometimes gives me a #REF! error as a result. Link here.

I checked all my cells and everything is in its place.

I'm posting this Excel sheet. The reference error occurs in column L, at 626 row and below.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Areen
  • 3
  • 3
  • Can you explain in words what you want the formula to do? – barry houdini Feb 10 '15 at 13:43
  • I want to find in $N626:$WO626 row a smallest value and use index function to find a corresponding value from $N$621:$WO$621 row (marked as light red) and put it as a result. – Areen Feb 10 '15 at 13:48

1 Answers1

1

Your formula will fail whenever the SMALL function returns a number that is not 0 (all rows in $N$621:$WO$621) or 1 (the first row in $N$621:$WO$621), because $N$621:$WO$621 only contains 1 row.

Did you mean to use the result of the SMALL as the column index?

Charles Williams
  • 23,121
  • 5
  • 38
  • 38