How to reference text from a range in one column depending on text in another column.

[RichPee]

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

[Richard Buttrey]

Hi,

Perhaps

=INDEX(A:A,MATCH("LATE",B:B,False),1)

[clabulis]

I noticed you have multiple people with a status of "OFF". See if you can adapt the setup in the attached file. Notice that the formula is surrounded by curly brackets. This means that it is an array formula and it must be confirmed with ctrl+shift+enter.

Book1.xlsx

[AlKey]

Maybe this

In D2 enter this array formula then copy it down and then to the right.

=IFERROR(INDEX($A:$A,SMALL(IF($B$1:$B$6=D$1,ROW($B$1:$B$6)),ROWS(D$1:D1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Row\Col	A	B	C	D	E	F	G	H
1	David	EARLY		EARLY	LATE	DAY	NIGHTS	OFF
2	Richard	LATE		David	Richard	Sarah	Daniel	Sam
3	Sarah	DAY						Marc
4	Sam	OFF
5	Daniel	NIGHTS
6	Marc	OFF

[RichPee]

Thanks for all the answers - Richard Buttrey yours worked perfectly. Thank you.