Re: Using INDEX twice??

From: Domenic (domenic22_at_sympatico.ca)
Date: 03/08/05 

Date: Tue, 08 Mar 2005 08:21:20 -0500

First off, rather than using the formula I offered, my preference is to
use the following formula instead...

=IF(H3="Yes",'MSP
Listing'!$C$2,INDEX(QuoteRecvd,SMALL(IF((PN=K3)*(UnitCost=S3)*(Supplier=W
3)*(Updated<>"Yes"),ROW(PN)-ROW($X$3)+1),1)))

...and then use conditional formatting to hide any error value that may
result when no criteria is met. I'm not sure if it's actually more
efficient, but it's definitely more appealing to look at. :)

Now, the formula...

Here, we're using the SMALL function to return the row number (relative
to the first cell in the range) which meets the specified criteria. The
row number is then used as an argument by the INDEX function.

So if we look at this part of the formula...

SMALL(IF((PN=K3)*(UnitCost=S3)*(Supplier=W3)*(Updated<>"Yes"),ROW(PN)-ROW
($X$3)+1),1)

...each of the conditional statements in the first argument of the IF
function returns an array of TRUE and FALSE. The second argument of the
IF function returns an array of numbers, representing the row number
relative to the first cell in the range.

The formula breaks down as follows...

(PN=K3) returns...

{TRUE;FALSE;TRUE;FALSE}

(UnitCost=S3) returns...

{TRUE;FALSE;TRUE;FALSE}

(Supplier=W3) returns...

{TRUE;FALSE;TRUE;FALSE}

(Updated<>"Yes") returns...

{FALSE;TRUE;TRUE;TRUE}

ROW(PN)-ROW($X$3)+1 returns...

{1;2;3;4}

Therefore, the IF function...

IF((PN=K3)*(UnitCost=S3)*(Supplier=W3)*(Updated<>"Yes"),ROW(PN)-ROW($X$3)
+1)

...returns the following array...

{FALSE;FALSE;3;FALSE}

Note that the numerical equivalents of TRUE and FALSE are 1 and 0. For
example...

TRUE*TRUE equals 1
FALSE*TRUE equals 0
TRUE*3 equals 3
FALSE+2 equals 2

As you can see, if the first argument is TRUE, the row number is
returned. Otherwise FALSE is returned. The SMALL function then returns
a value of 3, since that's the smallest number in the array.

Hope this helps!

In article <6756562D-40A3-4F9C-B901-72FB98597F73@microsoft.com>,
 "Joe Gieder" <JoeGieder@discussions.microsoft.com> wrote:

> Thank you for the help. Everything appears to be working perfectly. I'm
> trying to follow the way you came up with this but I'm lost. Can you help
> walk me through it please?
>
> Thanks
> Joe