Check for two conditions and return a third.

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,220
Good Morning Guys

I was wondering if someone could help me with this problem.

I am creating a spreadsheet to measure general abscence, to include holidays and sickness, one of the things I would like it to do is measure someone's sickness and alert the user if someone has been sick more than four times in the year. To do this I need to be able to capture the first date that a sickness occours, below is a representation of my spreadsheet (sorry, can't download and install the HTML maker)


A B C D E F G
Agent Name Emp ID No Start Date Hours Entitlement Absence Type Date

Pinky 1890 04/12/01 37.5 15 Holiday 01/12/2005
Pinky 1890 04/12/01 37.5 15 Sickness 02/12/2005
Perky 2330 07/10/03 37.5 150 Sickness 03/12/2005
Pinky 1890 04/12/01 37.5 150 Sickness 04/12/2005

I want to be able to look though Column A for "Perky" and Column F for "Sickness" and return the value in Column G (03/12/2005)

I started out trying to get the value with a VLOOKUP but I couldn't find an example on how to make it check for more than one value, is there any other way I can do this?

Thanks in advance for any help that can be provided. :)
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

To give you the match you ask for, try:

=INDEX(G2:G10,MATCH("Perky"&"@"&"Sickness",A2:A10&"@"&F2:F10,0))

Confirmed with Ctrl + Shift + Enter.


However to count instances I think

=SUMPRODUCT(--(A2:A10="Perky"),--(F2:F10="Sickness"))

could be useful. There you could also add additional conditions for dates if you want.


In both formulas it would be better to replace the text in quotes with references to cells holding the text.
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,760
Office Version
  1. 365
Platform
  1. Windows
Although there are many ways of achieving your requested outcome, sometimes a more simple solution is worthy of consideration..............

Have you thought of putting data filters at the top of the "Name" and "Absence" columns (just select the first cell in each column, then click from the top "Data"/ "Filter"/"AutoFilter")?

Then your users only have to click two boxes to get the required info.

Edit:
Sorry Fairwinds - didn't mean that your solution was too complicated- you just got there before me!!
 

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,220
Many Many Thank You's Fairwind - That was exactly what I wanted!

Thanks for the additional info with the counting, I had already done that, but I appreciate the thought :)

One question though (so I can understand), what is the "@" used for in the formula?

Sykes - Thanks for the input although that wasn't really what I was trying to achieve, I needed to be able to capture the date so I could use the DATEDIF command on it.

Thanks to you both :biggrin:
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
It is just to separate the two text strings in order to avoid false matches.

E.g. aaax & xxx could match with aaa & xxxx however putting @ or any other sign that are unlikely to appear in the strings would give aaax@xxx and thus prevent this.

You might not need this here if you know what your strings look like.
 

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,220
Ahhh .. I see now, that info may come in handy for something else that I need to do on another project, thanks for that!

Again :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,763
Messages
5,574,096
Members
412,567
Latest member
mm1
Top