Look up Values in different columns and return a value from a set column in the same array

[Grimace]

Hi All

I have a small challenge. In the attached sheet I am looking to use the data in the top table to return values into the cells C29 to D53.

If the team plays in the game (that is appears in either Column E, G, J or L) , I would like to simply write "Play" in column C (highlighted Yellow)

If the team has duty on the match, I would like to write "Duty" in column D in the yellow area beside that game number.

All others are to remain blank.

I have given an example by using conditional formatting, where the team is highlighted as either play or duty by chekcing against the value in cell B25. I just want to present this same info in a list format.

I am assuming it will be a MATCH type function, but am not too familiar with how they work.

Thanks in advance.

[Haseeb Avarakkan]

Try,

C29,

Please Login or Register  to view this content.

D29,

=IF(OR(COUNT(1/(VLOOKUP(B29,D$3:H$21,5,0)=B$25)),COUNT(1/(VLOOKUP(B29,I$3:M$21,5,0)=B$25))),"Duty","")

Then copy down.

[Grimace]

Perfect .... exactly what i was after !!

Thanks so much