Using Match with multiple criteria

[Joe Gieder]

First, thank you in advance for helping and sorry for the lenghty post, I
tried to be clear and give a sample of the data I use.
I use this array entered formula:
=IF(ISNA(MATCH($K3&$W3, PN&Supplier*(Quoted<>"Yes")*(Updated<>"Yes"),
0)),"",INDEX(Certs,MATCH($K3&$W3, PN
&Supplier*(Quoted<>"Yes")*(Updated<>"Yes"), 0)))
but it doesn't work. The problem is in the *(Quoted<>"Yes")*(Updated<>"Yes")
part because if I leave it out it works but it gives the wrong result. A
sample of the information I use is:

PN Certs Supplier Quoted Updated
01034AY218201-101 ABC Yes Yes
980452-101 ABC Yes Yes
B44222004-101 ABC Yes Yes
B44506001-101 XYZ Yes
B44506003-101 XYZ Yes
B44506004-107 XYZ Yes
B44506005-101 XYZ Yes
B44506006-101 XYZ Yes
B44506007-101 XYZ Yes
B44663041-101 ABC Yes Yes
B44663041-102 ABC Yes Yes
B44663075-101 ABC Yes Yes
B44663075-102 ABC Yes Yes
GSK112-1-C XYZ Yes
GSK90-1-C XYZ Yes
B44223090-101 ABC Yes Yes
B44506006-101 XYZ Yes
B44506001-101 XYZ Yes
B44506003-101 XYZ Yes
B44506004-107 XYZ Yes
B44506005-101 XYZ Yes
B44506007-101 XYZ Yes
01034DL218202-101 $550.00 ABC
944483-107 $550.00 ABC
B44222009-101 ABC Yes Yes
B44223094-103 ABC Yes Yes
GSK90-1-C $550.00 XYZ
B44506006-101 $550.00 XYZ
B44506003-101 $550.00 XYZ
B44506001-101 $550.00 XYZ
B44506007-101 $550.00 XYZ
B44506005-101 $550.00 XYZ
B44506004-107 $550.00 XYZ
GSK112-1-C $550.00 XYZ
01034AY218201-101 $100.00 ABC
980452-101 $100.00 ABC
B44222004-101 $100.00 ABC
B44223090-101 $100.00 ABC
B44663041-101 $100.00 ABC
B44663041-102 $100.00 ABC
B44663075-101 $100.00 ABC
B44663075-102 $100.00 ABC
B44223094-103 $100.00 ABC
B44222009-101 $100.00 ABC

On a separate tab I have these part numbers and suppliers:
944483-107 ABC
01034DL218202-101 ABC
01034AY218201-101 ABC
980452-101 ABC
B44222004-101 ABC
B44223090-101 ABC
GSK90-1-C XYZ
B44506006-101 XYZ
B44506003-101 XYZ
B44506001-101 XYZ
B44506007-101 XYZ
B44506005-101 XYZ
B44506004-107 XYZ
GSK112-1-C XYZ
B44663041-101 ABC
B44663041-102 ABC
B44663075-101 ABC
B44663075-102 ABC
B44222009-101 ABC
B44223094-103 ABC

What I'm trying to get the above formula to do is:
GSK90-1-C $550.00 XYZ
B44506006-101 $550.00 XYZ
B44506003-101 $550.00 XYZ
B44506001-101 $550.00 XYZ
B44506007-101 $550.00 XYZ
B44506005-101 $550.00 XYZ
B44506004-107 $550.00 XYZ
GSK112-1-C $550.00 XYZ
01034AY218201-101 $100.00 ABC
980452-101 $100.00 ABC
B44222004-101 $100.00 ABC
B44223090-101 $100.00 ABC
B44663041-101 $100.00 ABC
B44663041-102 $100.00 ABC
B44663075-101 $100.00 ABC
B44663075-102 $100.00 ABC
B44223094-103 $100.00 ABC
B44222009-101 $100.00 ABC

Joe

 

[Biff]

Hi!

What is the actual value of K3&W3?

PN&Supplier*(Quoted<>"Yes")*(Updated<>"Yes")

If that portion was constructed properly, that is the lookup_array for K3&W3

The proper syntax for something like that would be:

(PN&Supplier=some_logical_test)*(Quoted<>"Yes")*(Updated<>"Yes")

But that will return an array of TRUE'S (or 1's) and FALSES (or 0's) and
K3&W3 doesn't look like it evaluates to either.

Biff

 

[Biff]

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

 

[Joe Gieder]

Thank you. It works perfectly. To answer your previous question the value for
K3 is 944483-107 and for W3 is ABC.
What does the portion of the formula "MATCH(1,(" do?

Again, thank you
Joe