Hi ,
When ever you come across a formula using the IFERROR function , the first step is to eliminate the IFERROR function.
The IFERROR function in this case merely inserts the NA() error function result , which is #N/A , when ever the result of the actual formula is an error value.
The actual formula is :
(FIND($AF$18,$O$20:$O$119)>0)*($P$20:$T$119)
The FIND function is normally used as follows :
=FIND(find_text , within_text)
What this will do is return either the position within a cell contents ( within_text ) , where the looked up value ( find_text ) , is found or the #VALUE! error result if the cell contents do not contain the looked up value.
As an example , consider that $AF$18 contains the digit 1 ; the cell $O$20 contains the text Product Name 13.
The formula :
=FIND($AF$18 , $O$20)
will return the value 14 , signifying that the digit 1 occurs in the 14th position in the cell $O$20.
Suppose instead , we use the formula :
=FIND($AF$18 , $O$24)
will return the error value #VALUE! , signifying that the digit 1 does not occur anywhere in the cell $O$24 (Product Name 75).
Now , because the second parameter of the FIND function is not a single cell , but a range of cells , the result of this will be an array of results , which will contain either numbers or the #VALUE! error value.
Comparing this to 0 , using the check >0 will result in an array of TRUE or #VALUE! values ; this is because a numeric result from the FIND function will return TRUE while an error result will return the same #VALUE! error.
When used in an arithmetic operation , as in the multiplication by ($P$20:$T$119) , TRUE is replaced by 1 ; the final result will be an array of values from the range $P$20:$T$119 where ever the TRUE or 1 value was present , and the #VALUE! error value elsewhere.
This error value is then replaced by the #N/A value through the IFERROR function.
Since this is a multi-cell array formula , the entire output range AF20 through AJ119 is first selected , and then the formula is typed in and entered by pressing CTRL SHIFT ENTER. Excel displays the same formula in all the cells within this range , but calculates the individual cell values correctly.
Narayan