multiple condition Lookup in Excel
 

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

multiple condition Lookup in Excel
 

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

" wrote:

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

multiple condition Lookup in Excel
 

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



On 12 Mar, 14:19, BoniM wrote:
Please state your conditions. *The only sample you have given with the actual
conditions you say does not resolve things properly...



" wrote:
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- Hide quoted text -

- Show quoted text -