multiple condition Lookup in Excel

[s_j_wilkinson]

Hi,

I am trying to do a multiple condition lookup and am struggling to get
anywhere with it, could someone possibly provide some advice please. I
plan to use this for some conditional formatting on a worksheet.

OK I have a table with Staff details, absence start and end dates and
absence reason, and I want to carry out a lookup based on Name, start
and End Date, returning the reason.

the table I am working from looks like below

Surname Firstname Department StartDate EndDate
Duration Reason
Williams Frank 01/01/2007
02/01/2007 2 Holiday
Williams Frank 01/02/2007
02/02/2007 2 Holiday
Williams Frank 01/12/2006
02/12/2006 2 Sick
Bloggs Bill 02/02/2006
04/02/2006 3 Holiday
Butcher Wayne 01/05/2006
01/05/2006 1 Sick

The lookup will provide name details, and a date, so would need to
check if the date is between the start and end of an absence.

I have found a vba function called Mlookup after doing some research
which works fine until placed in a condition for conditional
formatting. then it really slows Excel down and I am not sure if it
still working or not.

I have tried the following but it seems unreliable, or is not
resolving things properly.

=OFFSET(NV1,SUM((NW2:NW777 &" "
&NV2:NV777=OG3)*(NY2:NY777<=OG4)*(OG4<=NZ2:NZ777)*(ROW(NV3:NV777)-
ROW(NV2)+1)),6)

NV = surname
NW = firstname
NY = StartDate
NZ = EndDate

Why I am developing this the following apply.
OG3 = Name
OG4 = Date

Oh by the way I am using Excel 2007

Many thanks in advance

Simon

 

[BoniM]

Please state your conditions. The only sample you have given with the actual
conditions you say does not resolve things properly...

 

[s_j_wilkinson]

The conditions used would be

a person's firstname,
a person's Surname,
a Date.

I am looking to return, the reason a person was absent on that
particular date.

This would then be used in conditional formatting to colour cells a
different colour according to the absence reason.

Thanks