Search for Text in Row & Return Value

[KarenG]

Hello,

My knowledge of formulas is very little and very, very basic so I'm struggling to look up "how to" information. I would be very appreciative if you could help me.

I'm trying to find the text in the row and then populate the cell in the last column with the same text as show below. The thought is that when looking down the far right column it's easy to pick out what location hasn't been visited during the specific week.

October​	Sun.​	Mon.​	Tues.​	Wed.​	Thurs.​	Fri.​	Sat.​
2023​	1​	2​	3​	4​	5​	6​	7​
location 1	sc​							sc​
location 2		kg​						kg​
location 3
location 4				rb​				rb​
location 5						js​		js​
location 6					ff​			ff​
location 7
location 8							sc	sc

Thank you in advance for your help.
Karen

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also do those cells contain a formula that returns "" or are they totally empty cells?

 

[Misca]

I made a littel table:

October	Sun.	Mon.	Tues.	Wed.	Thurs.	Fri.	Sat.
2023	1	2	3	4	5	6	7
location 1	sc							TRUE
location 2		kg						kg
location 3
location 4				rb				rb

If the main reason is to find out which location hasn't been visited I'd use conditional formatting with the formula in I3. That formats the rows where the columns are not empty (as you can see).
If you want the formula to return the actual value(s) the formula in I4 would be my pick. It is a really simple one and doesn't need error hadling for empty rows. It returns all the non-blank values in the range so it can return more than one value.
The formula in the last two rows would be my last pick. It must be wrapped it in an IFNA-function or you'll get N/A whenever the row is empty.

 

[Fluff]

Thanks for updating you profile, but can you answer the other question as well please.

 

[Misca]

Looks like my table missed the formulas and only showed the results. Here's another try. This time I swithed the conditional formatting to only show the empty rows.

Book1
	A	B	C	D	E	F	G	H	I
1	October	Sun.	Mon.	Tues.	Wed.	Thurs.	Fri.	Sat.
2	2023	1	2	3	4	5	6	7
3	location 1	sc							TRUE
4	location 2		kg						kg
5	location 3
6	location 4				rb				rb
Sheet1

Cell Formulas
Range	Formula
I3	I3	=COUNTA($B3:$H3)>0
I4	I4	=TEXTJOIN(", ",TRUE,B4:H4)
I5:I6	I5	=IFNA(INDEX(B5:H5,MATCH("zzz",B5:H5)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:H6	Expression	=COUNTA($B3:$H3)=0	text	NO

 

[Fluff]

Another option if the cells are empty

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	October	Sun.	Mon.	Tues.	Wed.	Thurs.	Fri.	Sat.
2	2023	1	2	3	4	5	6	7
3	location 1	sc							sc
4	location 2		kg						kg
5	location 3
6	location 4				rb				rb
7	location 5						js		js
8	location 6					ff			ff
9	location 7
10	location 8							sc	sc
Data

Cell Formulas
Range	Formula
I3:I10	I3	=IFERROR(TOROW(B3:H3,1),"")

 

[KarenG]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also do those cells contain a formula that returns "" or are they totally empty cells?

The daily cells are empty so the result would return an empty cell in the far right column.

 

[Fluff]

Have you tried the various options suggested?

 

[KarenG]

Just tried them and your solution worked so I marked it as being solved. Thank you so much. Now I'm off to research the ins and outs of your formula so I can understand how it works. Thanks again for your help.

 

[Fluff]

Glad we could help & thanks for the feedback.