Vlookup and offset

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DonH - 21 Oct 2006 09:50 GMT

Hi,

I would like to lookup a users ID in a table and check if they have a
paticular qualification, a normal VLOOKUP will do that but then I would like
to refer to a date in the header to see when they gained the qualification
so the cell can determine whether they are qualified based on the current
date. I could do this without checking the date but that would disguise the
lack of skills for the rest of the past records as soon as the skill was
recorded.

I think I need to combine an IF, VLOOKUP and an OFFSET but am at a loss as
to how to put it all together.

Hope someone can help.

Thanks in anticipation.

Don

Reply to this Message

ScottO - 21 Oct 2006 10:28 GMT

Would an "AND" function do it?
S

| Hi,
|
[quoted text clipped - 14 lines]
|
| Don

Reply to this Message

Roger Govier - 21 Oct 2006 13:27 GMT

Hi Don

Assuming your reference date was in cell A1
perhaps something like the following
=IF($A$1-VLOOKUP(ID,Table,offset,0)<x_days,"Valid","Invalid")

I am assuming the value in the Table is the date of their qualification.
If whether they hold a particular qualification is held in one column of
the table, and date of acquiring the qualification is held in the next
column, you need only concern yourself with the date column, since if
they don't hold the qualification then presumable the date cell would be
blank.

If date is blank, Excel will interpret that as 01/01/1900 and when taken
away from your reference date would give a huge value which would cause
an "Invalid" outcome.

Signature

Regards

Roger Govier

> Hi,
>
[quoted text clipped - 14 lines]
>
> Don

Reply to this Message

DonH - 22 Oct 2006 10:47 GMT

Many thanks for your reply I'll give it a go.

Regards

DonH

> Hi Don
>
[quoted text clipped - 30 lines]
>>
>> Don

Reply to this Message