lookup date in column, search columns to right for name, return "x"

I have two worksheets. Sheet1 has dates in Col A and names in several columns (up to 8) to the right. Sheet 2 has all possible names in Col A, then dates (1-31) in columns to right. What I need is to put an "X" in the proper column for which day they pull shift. VLOOKUP works for column specific, ie. =vlookup(B2,sheet1!A1:A31,2,false) for whatever name it finds in Col 2, but I want it to look up the date in Col A Sheet 2, look for Bob in columns to right and return an "X" in cell for that day in Sheet2.


Thanks

IGNORE THIS... see next thread instead... Fotis has a great solution... nice work!

Hi roothog,

Convert your data first... follow this thread...

http://www.excelforum.com/excel-prog...-vertical.html

Once you have the new data set, simply use pivot table to accomplish what you need.

Dennis

In B2 and copy down and across.

=IF(SUMPRODUCT((Sheet1!$B$1:$E$3=Sheet2!$A2)*(Sheet1!$A$1:$A$3=Sheet2!B$1))>0,"X","")

You are welcome.

Thanks for the reb * and for your kind comments.

@Dennis

Thank you too for your kind comments.