Return value from range that does not include specific text

[john-paul]

Hi,

I need to return the first value from a range that does not include "See role ...", "Acting in ...", or is blank, based on an input of "Role" number.

Result should be:
10 = Sam Smith
11 = Sam Smith
12 = Bob Brown
13 = Robert Roberts
14 = Walter Woods
15 = Walter Woods
16 = David Davis

Also, is it possible to switch the search to start right to left, so that role 11 picks up Jane Jones, and role 15 picks up Mark Michael, ie: first non-blank, not including "See role ..." or "Acting in.." from right to left?

Thank you
John-Paul

Role	Role Owner	Covered By	Temporary Worker
10	See role 11	Sam Smith
11	Sam Smith	Acting in role 10	Jane Jones
12	Bob Brown
13			Robert Roberts
14	See role 15	Walter Woods
15	Walter Woods	Acting in role 14	Mark Michael
16	See role 99	David Davis

 

[awoohaw]

I don't understand your scenario.
Lines 10 & 11 have "See role" or "Acting in" yet you want Sam Smith to show up, what qualifier have I missed?

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F
1	Role	Role Owner	Covered By	Temporary Worker	Left to right	Right to Left
2	10	See role 11	Sam Smith		Sam Smith	Sam Smith
3	11	Sam Smith	Acting in role 10	Jane Jones	Sam Smith	Jane Jones
4	12	Bob Brown			Bob Brown	Bob Brown
5	13			Robert Roberts	Robert Roberts	Robert Roberts
6	14	See role 15	Walter Woods		Walter Woods	Walter Woods
7	15	Walter Woods	Acting in role 14	Mark Michael	Walter Woods	Mark Michael
8	16	See role 99	David Davis		David Davis	David Davis
Sheet2

Cell Formulas
Range	Formula
E2:E8	E2	=XLOOKUP(1,(B2:D2<>"")*ISERROR(SEARCH("role",B2:D2)),B2:D2,,,1)
F2:F8	F2	=XLOOKUP(1,(B2:D2<>"")*ISERROR(SEARCH("role",B2:D2)),B2:D2,,,-1)

If you want both left to right and right to left in one formula, you can use an array like this.

=XLOOKUP(1,(B2:D2<>"")*ISERROR(SEARCH("role",B2:D2)),B2:D2,,,{1,-1})

 

[john-paul]

Thanks Cubist that is great!
If I had an input cell (H1) to select an individual role number can the formula be adjusted so it returns the workers name in I1 (L to R) and J1 (R to L)?

 

[Cubist]

Like this?

Book3
	A	B	C	D			G	H	I	J
1	Role	Role Owner	Covered By	Temporary Worker				11	Sam Smith	Jane Jones
2	10	See role 11	Sam Smith
3	11	Sam Smith	Acting in role 10	Jane Jones
4	12	Bob Brown
5	13			Robert Roberts
6	14	See role 15	Walter Woods
7	15	Walter Woods	Acting in role 14	Mark Michael
8	16	See role 99	David Davis
Sheet3

Cell Formulas
Range	Formula
I1:J1	I1	=LET(f,FILTER(B2:D8,A2:A8=H1),XLOOKUP(1,(f<>"")*ISERROR(SEARCH("role",f)),f,,,{1,-1}))
Dynamic array formulas.

 

[john-paul]

perfect - thanks again.