To Biff: Using Match with multiple criteria

[Guest]

Thank you for the help it worked perfectly. What Does the "MATCH(1,(" mean in
the below formula?

Thaks
joe

 

[Biff]

Hi!

It means that when all these elements are multiplied together:

(PN&Supplier=$K3&$W3)*(Quoted<>"Yes")*(Updated<>"Yes")

the operation returns an array of 1's and 0's. Something like this:

0
0
0
1
0
0
0

Then:

MATCH(1,{0,0,0,1,0,0,0},0) = 4

Because the matched 1 is in the 4th position of the lookup_array. And then
returns the 4th element of the Index array.

Pretty simple stuff, eh!

Now, can you answer the question I just posted? <g> It's related!

Biff

 

[Guest]

Thanks. I do have one problem. It works fine when there's a value to find but
when there's not value it returns #N/A. How would I fix this?

TIA
Joe

 

[Biff]

Hi!

There are a couple of ways.

If your file is large and you have 1,000's of rows of data that are
referenced in 1,000's of these type formulas I would use conditional
formatting to hide the #N/A's.

Select the range of these formulas. Assume this range is A1:A1000.

Goto Format>Conditional Formatting
Formula is: =ISERROR(A1) or =ISNA(A1)
Set the cells font color to be the same as the background fill color.

The other way is to include an error trap in the formula itself. The
disadvantage to this method is that it makes the formula twice as long
(complex) and takes twice as long to process which can slow things down if
as I said above, the file is large.

=IF(ISNA(MATCH(1,(PN&Supplier=$K3&$W3)*(Quoted<>"Yes")*(Updated<>"Yes"),0)),"",INDEX(Certs,MATCH(1,(PN&Supplier=$K3&$W3)*(Quoted<>"Yes")*(Updated<>"Yes"),0)))

This formula will leave the cell "blank" if no match is found. If you might
want a zero returned, just replace the "" in the formula.

Biff

 

[Guest]

Thanks. Both work perfectly. I opted for the longer approach.

Joe

 

[Biff]

Good deal! Thanks for the feedback.

Biff

Thanks. Both work perfectly. I opted for the longer approach.

Joe