Vlookup and Countifs??? (I think?)

[Mark McInerney]

Hi All,

I have a column with employee names (A1:A5), and to the right of these names are the days that they have requested off (B1:F5).

	A	B	C	D	E	F
1	John	1/1/19	5/2/19
2	Mark	3/6/19	10/10/19
3	Barry	22/5/19	31/12/19	15/4/19	17/8/19	3/8/19
4	Kevin	6/6/19
5	Clare	29/6/19

<tbody>
</tbody>

On another area of the worksheet there is a employee schedule.

100	A	B	C	D	E	F	G	H
101		1/1/19	2/1/19	3/1/19	4/1/19	5/1/19	6/1/19	7/1/19
102	John	****

<tbody>
</tbody>

Cell A102 is a drop down linked to the data in a1:a5. I need to be able to conditional format cell b102, as the date stored in b101, is in the row of data from a1:f1.

The value in cell a102 can and will change.

Any help appreciated.

Thanks - Mark.

 

[Fluff]

Select B102 to end of that row & use
=MATCH(B$101,INDEX($B$1:$F$5,MATCH($A102,$A$1:$A$5,0),0),0)

 

[sadboy309]

I use formula as picture for range ("B12:H13"), you can use for range a lot.

Formula as:

[B12]=NOT(ISERROR(MATCH(B$11,INDIRECT("B" & MATCH($A12,$A$1:$A$5,0) & ":F" & MATCH($A12,$A$1:$A$5,0)),0)))

As Picture:

 

[Mark McInerney]

Hi Guys,

Thanks so much for taking the time to review my query - worked a treat! Many Thanks again - appreciated.

Best - Mark.

 

[Fluff]

You're welcome & thanks for the feedback