To Biff: Using Match with multiple criteria
 

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

Thaks
joe


Biff" wrote:

After thinking about it some more, this may be what you want:

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

Biff

Hi!

It means that when all these elements are multiplied together:

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

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

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

Thaks
joe


Biff" wrote:

After thinking about it some more, this may be what you want:

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

Biff

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" wrote:

Hi!

It means that when all these elements are multiplied together:

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

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

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

Thaks
joe


Biff" wrote:

After thinking about it some more, this may be what you want:

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

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 FormatConditional 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<"Y es")*(Updated<"Yes"),0)),"",INDEX(Certs,MATCH(1,( PN&Supplier=$K3&$W3)*(Quoted<"Yes")*(Updated<"Ye s"),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

"Joe Gieder" wrote in message
...
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" wrote:

Hi!

It means that when all these elements are multiplied together:

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

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

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

Thaks
joe


Biff" wrote:

After thinking about it some more, this may be what you want:

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

Biff

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

Joe

"Biff" wrote:

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 FormatConditional 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<"Y es")*(Updated<"Yes"),0)),"",INDEX(Certs,MATCH(1,( PN&Supplier=$K3&$W3)*(Quoted<"Yes")*(Updated<"Ye s"),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

"Joe Gieder" wrote in message
...
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" wrote:

Hi!

It means that when all these elements are multiplied together:

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

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

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

Thaks
joe


Biff" wrote:

After thinking about it some more, this may be what you want:

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

Biff

Good deal! Thanks for the feedback.

Biff

"Joe Gieder" wrote in message
...
Thanks. Both work perfectly. I opted for the longer approach.

Joe

"Biff" wrote:

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 FormatConditional 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<"Y es")*(Updated<"Yes"),0)),"",INDEX(Certs,MATCH(1,( PN&Supplier=$K3&$W3)*(Quoted<"Yes")*(Updated<"Ye s"),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

"Joe Gieder" wrote in message
...
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" wrote:

Hi!

It means that when all these elements are multiplied together:

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

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

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

Thaks
joe


Biff" wrote:

After thinking about it some more, this may be what you want:

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

Biff