| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Using Match with multiple criteria
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 |
|
#2
|
|||
|
|||
|
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")*(U pdated<"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 "Joe Gieder" wrote in message ... 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 |
|
#3
|
|||
|
|||
|
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 "Biff" wrote in message ... 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")*(U pdated<"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 "Joe Gieder" wrote in message ... 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 |
|
#4
|
|||
|
|||
|
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 "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 "Biff" wrote in message ... 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")*(U pdated<"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 "Joe Gieder" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| match multiple criteria & return value from array | Excel Worksheet Functions | |||
| Multiple Criteria IF Nesting | Excel Worksheet Functions | |||
| Finding a record using multiple combo boxes as my search criteria | Excel Discussion (Misc queries) | |||
| Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) | |||
| Counting "rows", i.e. simultaneous criteria for multiple cells | Excel Worksheet Functions | |||
Linear Mode
