Re: To Biff: Using Match with multiple criteria

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

"Joe Gieder" <JoeGieder@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1269DA3A-F33C-4B06-8A47-E27C4F3115E0@xxxxxxxxxxxxxxxx
> 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<>"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
>>
>> "Joe Gieder" <JoeGieder@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:43EB2EDC-E4B0-4AC6-A8E9-3EF33A4E4B1B@xxxxxxxxxxxxxxxx
>> > 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
>> >>
>> >
>>
>>
>>


.

Relevant Pages