Translate in English please...

[Guest]

I have this excel form that gathers data from an external source and
references other columns in other sheets. I do not understand what this is
trying to accomplish

=IF(ROWS($1:1)<=COUNTIF(J$2:J$645,'Budget
Input'!B$5),INDEX(K$2:K$645,SMALL(IF(J$2:J$645='Budget
Input'!B$5,ROW(K$2:K$645)-ROW(K$2)+1),ROWS($1:1))),"")


I think it says for a row (with $1 being variable) Count the column J and
index them against column K, if a value in J equals Budget Input worksheet
cell B5, then match the existing rows in K - ...here is where I am lost...

 

[David Empey]

I have this excel form that gathers data from an external source and
references other columns in other sheets. I do not understand what
this is trying to accomplish

=IF(ROWS($1:1)<=COUNTIF(J$2:J$645,'Budget
Input'!B$5),INDEX(K$2:K$645,SMALL(IF(J$2:J$645='Budget
Input'!B$5,ROW(K$2:K$645)-ROW(K$2)+1),ROWS($1:1))),"")


I think it says for a row (with $1 being variable) Count the column J
and index them against column K, if a value in J equals Budget Input
worksheet cell B5, then match the existing rows in K - ...here is
where I am lost...

Looks like it's returning all the items in K2:K645 such that
the corresponding value in J2:J645 is equal to the value
in 'Budget Input'!B$5.

You'd enter it as a single-cell array formula and copy it down.

ROWS($1:1) gives a count of the rows between your starting row
and the current row, whatever it is.

COUNTIF() counts the items in the J range equal to the value
in B5.

So the outer IF() says, if the number of rows is less than or
equal to the number of items in J2:J645 that match the
Budget Input!B5 value, then return the INDEX() function, else
return "".

The INDEX returns an element from the range K2:K645.
Which element? The element whose row is computed by the
SMALL() function.

SMALL() takes an array and a rank number k, and returns
the k-th smallest element of the array.

'k' here can be seen to be the row number: ROWS($1:1)

In this case, the array is created by the inner IF() formula.
This formula must be entered as a single-cell array formula
so the IF() will create an array of results.

The inner IF() returns 2 options:
1) the element number of an item from K2:K645,
the number is computed by the expression
ROW(K$2:K$645)-ROW(K$2)+1
this returned when the value in column J = the value in B5
or
2) the value FALSE

Since FALSE sorts after any number, the SMALL() function
gives the item number of the k-th item in the K2:K645 range
such that the value in the J2:J645 range matches the value in B5.

Then the INDEX() function (remember it?) returns the value
from the K2:K645 range with that item number.