Hi,

I am trying to create a formula that will display text from a range of cells in a column depending on text in another range. For example -

A B
1 David EARLY
2 Richard LATE
3 Sarah DAY
4 Sam OFF
5 Daniel NIGHTS
6 Marc OFF

I want a separate cell that will for example, display Richard because he is on LATE. I thought =COUNTIF(b1:b6, "LATE", A1:A6) might work, but this doesn't display the text as it isn't a number. Does a formula exist for this?

Many thanks,

Richard