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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 11:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com