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 |
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 |