Check for two conditions and return a third.

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,233
Office Version
  1. 365
Platform
  1. Windows
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. :)
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Upvote 0
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!!
 
Upvote 0
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:
 
Upvote 0
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.
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,217,332
Messages
6,135,946
Members
449,974
Latest member
riffburn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top