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).

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]John[/TD]
[TD]1/1/19[/TD]
[TD]5/2/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Mark[/TD]
[TD]3/6/19[/TD]
[TD]10/10/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Barry[/TD]
[TD]22/5/19[/TD]
[TD]31/12/19[/TD]
[TD]15/4/19[/TD]
[TD]17/8/19[/TD]
[TD]3/8/19[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Kevin[/TD]
[TD]6/6/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Clare[/TD]
[TD]29/6/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]100[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD][/TD]
[TD]1/1/19[/TD]
[TD]2/1/19[/TD]
[TD]3/1/19[/TD]
[TD]4/1/19[/TD]
[TD]5/1/19[/TD]
[TD]6/1/19[/TD]
[TD]7/1/19[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]John[/TD]
[TD]****[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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