Using INDEX twice??

[Joe Gieder]

First off sorry for the lengthy post. I hope I was clear, if not please let
me know. I was wondering, can INDEX be used twice in a row and is tis even
the right direction? This is the current formula I use:
array entered
=IF(H3="Yes",'MSP
Listing'!$C$2,IF(ISNA(MATCH($K3&S3&$W3,PN&UnitCost&Supplier,0)),"",INDEX(QuoteRecvd,MATCH($K3&$S3&$W3,PN&UnitCost&Supplier,0))))

Sample data I use
worksheet1 (Results)
H K S W X
3 1234 $5.25 xyz co formula here

worksheet2 (Data)
I AB AM AS BG
P/N UnitCost Supplier QuoteRecvd Updated
3 1234 $5.25 xyz co Yes
4 2345 $2.68 abc co 10/21/04
5 1234 $5.25 xyz co 01/05/05
6 1256 $8.25 def co 01/24/05

I need to lookup the following:
MATCH K&S&W (pn, unitcost & supplier) with worksheet2 while INDEXing
Quoterecvd, hence the above formula which retrieves the date. Now I need to
add one more criteria within this formula that will look in the Updated
column and if it says YES ignore it and proceed to the next MATCH. Can this
be done?

Thanks in advance for your help.
Joe

 

[Domenic]

Assuming that you define Column BG with the name 'Updated', try the
following formula...

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

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Joe Gieder]

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

 

[Domenic]

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!