Mutiple match criteria

[Peter]

Here is my problem;

Trying to match a six digit badge number (may or may not be on the
list it is matching to) to a table that contains a list of specific
badge numbers.

If that particular badge number matches on the list data I want it to
return a match. However, if it matches I need to add another criteria
to the formula. In the list that contains the specific badge numbers
is a date column. I want it to return "Yes" if that badge matches and
is <= to a certain time frame. If it doesn't fall within that date
range I want it to return "No".

Not sure which route to take, Vlookup, indexmatch, &if.........

Any help is much appreciated!!

Regards,

Peter

 

[Bob Phillips]

=_IF(ISNUMBER(MATCH(1,(rngBadges=badge_num)*(rngDates>=start_date)*(rngDates<+end_date),0)),"","No")

this is an array function, so commit with Ctrl-Shift-Enter, not just Enter.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Not sure in the lack of specifics, just some thoughts ..

Assuming real dates in col B, badge numbers in col A, then with lookup badge
numbers listed in D2 down perhaps something like this in say, E2:

=IF(ISNA(MATCH(D2,A:A,0)),"",IF(INDEX(B:B,MATCH(D2,A:A,0))<=TODAY()-30,"Yes","No"))

Above would return "Yes" or "No" depending on whether the date returned by
the INDEX/MATCH is more than 30 days ago.