Solved

Excel searching column multiple conditions

Posted on 2012-04-13
9
159 Views
Last Modified: 2012-04-15
Hi All,

I have a question regarding searching an unordered list of data with multiple conditions. I have attached a file of what I am trying to do.

Essentially I want to search column G, total sales, for the first value that meets 2 (or potentially more) conditions.

In the example i have highlighted in yellow, I am looking to return the first date where the date is greater than April 1 AND the Total Sales exceeds 150.

I have been able to do this with a single condition, highlighted in orange (or whatever you would like to call that color). The formula I am using there is:

=INDEX(Sales[Date],MATCH(TRUE,Sales[Total Sales]>=150,0))

Open in new window


I just can't seem to get this to work with AND statements.

In this example there are 2 conditions, but I may like to include more in the future.

I am just looking for the simplest and most elegant way to do this.

Many thanks in advance
0
Comment
Question by:stokenator
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 7

Expert Comment

by:karunamoorthy
ID: 37843845
where is the attached file?
0
 

Author Comment

by:stokenator
ID: 37844303
Attached is the sample file - my apologies.

I have highlight the expected result in yellow. many thanks
sample-file.xlsx
0
 
LVL 81

Expert Comment

by:byundt
ID: 37845129
Assuming that there is an answer, you might try an array formula like:
=INDEX(F$2:F$1000,MATCH(1,(F$2:F$1000>=--"4/1/12")*(G$2:G$1000>=150),0))

To array-enter a formula:
1) Select the cell and paste the formula in the formula bar
2) Hold Control and Shift keys down
3) Hit Enter, then release all three keys

Excel should respond by putting curly braces { } surrounding your formula. If not (or if you see #N/A), then repeat steps 1-3.


If you need to verify that there is an answer before returning the date, then try array-entering:
=IF(COUNTIFS(F$2:F$1000,">=4/1/12",g$2:G$1000,">=150")=0,"",INDEX(F$2:F$1000,MATCH(1,(F$2:F$1000>=--"4/1/12")*(G$2:G$1000>=150),0)))

Brad
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Expert Comment

by:dlmille
ID: 37845603
Brad - I just learned something

--"4/1/12" converts to date, like doing DATEVALUE("4/1/12")

A bit cryptic but easy on the typing ;)

Dave
0
 
LVL 81

Expert Comment

by:byundt
ID: 37846154
Dave,
I've been using that short-cut with dates for so long, I forget that it isn't the official way of doing things. :-)         One thing I don't know, however, is how it works for people who have d-m-yy as the default date structure.

Brad


On the original question, I forgot about using IFERROR. Using it will shorten the alternative formula to:
=IFERROR(INDEX(F$2:F$1000,MATCH(1,(F$2:F$1000>=--"4/1/12")*(G$2:G$1000>=150),0)),"")

It still must be array-entered.
0
 

Author Comment

by:stokenator
ID: 37848432
Thankyou so much Brad, that is perfect!

There is one other task that i need to adapt this formula to accomplish and I can't seem to do it. I hope that you're able to assist.

I have uploaded a new version of my sample file. The expected value is highlighted in RED.

After I return the date from the first example, I want to calculate the minimum sales between the 2 dates.

Simply put I want to search the list and return the minimum sales between 2 dates.

I would really appreciate it if you could assist with this as well.

Regards,

Stokenator
sample-file-V2.xlsx
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 37848606
Matt,
I see that you put your sales data in a table. That's a good practice, so I rewrote the original formula plus the newly requested one as:
=INDEX(Sales[Date],MATCH(1,(Sales[Date]>=--"4/1/12")*(Sales[Total Sales]>=150),0))   first over 150
=MIN(IF((Sales[Date]>=--"4/1/12")*(Sales[Date]<=B3),Sales[Total Sales],""))    min sales
=INDEX(Sales[Date],MATCH(B5,IF((Sales[Date]>=--"4/1/12")*(Sales[Date]<=B3),(Sales[Total Sales]),""),0))     date of min sales

All of these are array formulas, so remember to Control + Shift + Enter.

Cell B3 contains the results of the first formula. Note you'll need to apply a date format to the result of the third formula.

Brad
0
 

Author Comment

by:stokenator
ID: 37848661
Brad - THANKYOU!

This is brilliant and just what I was looking for.
0
 

Author Closing Comment

by:stokenator
ID: 37848664
perfect solution
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question