Sheet 1. A - name. B - start of vacation. C - end of vacation.
Sheet 2. A - name. B - start of vacation. C - end of vacation. D - alternate
contact person.

when i hover over a name in sheet 1, or click on it (or whatever, doesn't
matter), i need it to find the same entry in sheet 2 (name, vac start, vac
end) and then tell me the alternate contact person. BUT, each name may occur
more than once in column A, so i can't use vlookup to find the first
occurence, since it may not be the correct occurence.

secondly, it must then search sheet 2 column A (or sheet 1 column A) to find
the name of the alternate contact person, and see if they are on vacation at
the same time as the initial person selected, and notify if there is a
conflict.

sheet 1 and sheet 2 columns A B and C are identical, BUT the rows are not
the same. each person in sheet 1 only takes up 1 row, but each person in
sheet 2 may take up multiple rows (so i can't just jump over to sheet 2 and
use the same row, i have to search through the list).

any help appreciated! i could probably do it with loops, my own "lookup"
function, but since the total rows that will eventually be used is an unknown
quantity, i have to give it an insane number like 1000 and that makes it very
slow. if only vlookup was non-terminating!