| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple search criteria to return one response
Trying to return a single response from three different pieces of
information. The information that is provided is the "Year" the "Type" and the "Grade". The problem is that there is multipe repeats for "Year' and "Type" with occassional repeats in "Grade". The search needs all three data points to determine the earlies "Available" date. YEAR TYPE GRADE AVAILABLE 2008 Apple A 11/07/2005 2008 Apple AD 28/11/2005 2008 Apple B 22/01/2005 2008 Apple CA 12/03/2005 2008 Apple CB 11/06/2007 2009 Apple A 26/02/2007 2009 Apple B 19/11/2007 2009 Apple C 18/02/2008 2008 Apple AD 28/11/2005 2008 Apple B 22/01/2005 2008 Pear A 11/07/2005 2008 Pear AD 28/11/2005 2008 Pear B 04/12/2006 2008 Pear C 05/03/2007 2009 Pear A 26/02/2007 2009 Pear B 10/12/2007 2009 Pear C 17/03/2008 How do you conduct a lookup with multiple search criteria? Thanks Dan |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple search criteria to return one response
Hi!
Try this: F1 = 2008 (year) G1 = apple (type) H1 = A (grade) Formula entered as an array using the key combo of CTRL,SHIFT,ENTER: =MIN(IF((A2:A18=F1)*(B2:B18=G1)*(C2:C18=H1),D2:D18 )) Format the cell as DATE Biff "Dan" wrote in message ... Trying to return a single response from three different pieces of information. The information that is provided is the "Year" the "Type" and the "Grade". The problem is that there is multipe repeats for "Year' and "Type" with occassional repeats in "Grade". The search needs all three data points to determine the earlies "Available" date. YEAR TYPE GRADE AVAILABLE 2008 Apple A 11/07/2005 2008 Apple AD 28/11/2005 2008 Apple B 22/01/2005 2008 Apple CA 12/03/2005 2008 Apple CB 11/06/2007 2009 Apple A 26/02/2007 2009 Apple B 19/11/2007 2009 Apple C 18/02/2008 2008 Apple AD 28/11/2005 2008 Apple B 22/01/2005 2008 Pear A 11/07/2005 2008 Pear AD 28/11/2005 2008 Pear B 04/12/2006 2008 Pear C 05/03/2007 2009 Pear A 26/02/2007 2009 Pear B 10/12/2007 2009 Pear C 17/03/2008 How do you conduct a lookup with multiple search criteria? Thanks Dan |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple search criteria to return one response
Hello Biff:
It appears to work. One correction to the formula, for the non value inputs, i.e. apple and grade, they needed to be in "quotes" for the IF statement to accept them. Thanks Dan "Biff" wrote: Hi! Try this: F1 = 2008 (year) G1 = apple (type) H1 = A (grade) Formula entered as an array using the key combo of CTRL,SHIFT,ENTER: =MIN(IF((A2:A18=F1)*(B2:B18=G1)*(C2:C18=H1),D2:D18 )) Format the cell as DATE Biff "Dan" wrote in message ... Trying to return a single response from three different pieces of information. The information that is provided is the "Year" the "Type" and the "Grade". The problem is that there is multipe repeats for "Year' and "Type" with occassional repeats in "Grade". The search needs all three data points to determine the earlies "Available" date. YEAR TYPE GRADE AVAILABLE 2008 Apple A 11/07/2005 2008 Apple AD 28/11/2005 2008 Apple B 22/01/2005 2008 Apple CA 12/03/2005 2008 Apple CB 11/06/2007 2009 Apple A 26/02/2007 2009 Apple B 19/11/2007 2009 Apple C 18/02/2008 2008 Apple AD 28/11/2005 2008 Apple B 22/01/2005 2008 Pear A 11/07/2005 2008 Pear AD 28/11/2005 2008 Pear B 04/12/2006 2008 Pear C 05/03/2007 2009 Pear A 26/02/2007 2009 Pear B 10/12/2007 2009 Pear C 17/03/2008 How do you conduct a lookup with multiple search criteria? Thanks Dan |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple search criteria to return one response
Hi!
It appears to work. One correction to the formula, for the non value inputs, i.e. apple and grade, they needed to be in "quotes" for the IF statement to accept them. Yes, if you hard code those values into the formula. It's much more versatile if you use cells to hold the criteria and then refer to those cells. Biff "Dan" wrote in message ... Hello Biff: It appears to work. One correction to the formula, for the non value inputs, i.e. apple and grade, they needed to be in "quotes" for the IF statement to accept them. Thanks Dan "Biff" wrote: Hi! Try this: F1 = 2008 (year) G1 = apple (type) H1 = A (grade) Formula entered as an array using the key combo of CTRL,SHIFT,ENTER: =MIN(IF((A2:A18=F1)*(B2:B18=G1)*(C2:C18=H1),D2:D18 )) Format the cell as DATE Biff "Dan" wrote in message ... Trying to return a single response from three different pieces of information. The information that is provided is the "Year" the "Type" and the "Grade". The problem is that there is multipe repeats for "Year' and "Type" with occassional repeats in "Grade". The search needs all three data points to determine the earlies "Available" date. YEAR TYPE GRADE AVAILABLE 2008 Apple A 11/07/2005 2008 Apple AD 28/11/2005 2008 Apple B 22/01/2005 2008 Apple CA 12/03/2005 2008 Apple CB 11/06/2007 2009 Apple A 26/02/2007 2009 Apple B 19/11/2007 2009 Apple C 18/02/2008 2008 Apple AD 28/11/2005 2008 Apple B 22/01/2005 2008 Pear A 11/07/2005 2008 Pear AD 28/11/2005 2008 Pear B 04/12/2006 2008 Pear C 05/03/2007 2009 Pear A 26/02/2007 2009 Pear B 10/12/2007 2009 Pear C 17/03/2008 How do you conduct a lookup with multiple search criteria? Thanks Dan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| return multiple rows of data based on criteria | Excel Worksheet Functions | |||
| How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions | |||
| Multiple Criteria IF Nesting | Excel Worksheet Functions | |||
| sumproduct using multiple criteria | Excel Worksheet Functions | |||
| search multiple worksheets for an item and return the Wsheets name | Excel Worksheet Functions | |||
Linear Mode
