Vlookup and offset

[DonH]

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

 

[ScottO]

Would an "AND" function do it?
S

| 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
|
|

 

[Roger Govier]

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.

 

[DonH]

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

Regards

DonH