return non blank values in a row with additional logic

[Dervos]

Hello Everyone,

Seeking for assistance to help solve scenario below...

I am looking for a formula which would return a non-blank value in a row and cover some additional logic:

1) if the row has one value (i.e. ROW 2 / B2) return that value
2) if the row has two or more values (i.e. ROW3 / C3&D3) return specific text
3) if the row has no values (i.e. ROW4) return specific text (2)

*Please note that the actual cells are filled with formulas returning string values in a General format.



Appreciate your help!

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this, copied down, does what you want.

Excel Workbook
	A	B	C	D	E
1
2	X1	yellow			yellow
3	X2		red	yellow	Error
4	X3				No results
Check

BTW, for the future, look at the link in my signature block below for ways to post sample data that we can actually copy/paste from to test.

 

[Dervos]

Peter,

Thanks for the tip!

Unfortunately, the suggested formula is returning "error" value across all the rows... I think this may be due to the cell format which I'll try to explain.

I am using below formulas to get the outputs (yellow/red/...) in Columns B/C/D:

	A	B	C	D	E
1​		color1	color2	color3
2​	text : yellow x	=IF((SUMPRODUCT(--ISNUMBER(SEARCH(Table73[[#All],[yellow]], A2)))>0)=TRUE,Table73[[#Headers],[yellow]], "")	=IF((SUMPRODUCT(--ISNUMBER(SEARCH(Table74[[#All],[red]], A2)))>0)=TRUE,Table74[[#Headers],[red]], "")	=IF((SUMPRODUCT(--ISNUMBER(SEARCH(Table76[[#All],[blue]], A2)))>0)=TRUE,Table76[[#Headers],[blue]], "")
3​	text : red yellow	=IF((SUMPRODUCT(--ISNUMBER(SEARCH(Table73[[#All],[yellow]], A3)))>0)=TRUE,Table73[[#Headers],[yellow]], "")	=IF((SUMPRODUCT(--ISNUMBER(SEARCH(Table74[[#All],[red]], A3)))>0)=TRUE,Table74[[#Headers],[red]], "")	=IF((SUMPRODUCT(--ISNUMBER(SEARCH(Table76[[#All],[blue]], A3)))>0)=TRUE,Table76[[#Headers],[blue]], "")
4​	text : empty	=IF((SUMPRODUCT(--ISNUMBER(SEARCH(Table73[[#All],[yellow]], A4)))>0)=TRUE,Table73[[#Headers],[yellow]], "")	=IF((SUMPRODUCT(--ISNUMBER(SEARCH(Table74[[#All],[red]], A4)))>0)=TRUE,Table74[[#Headers],[red]], "")	=IF((SUMPRODUCT(--ISNUMBER(SEARCH(Table76[[#All],[blue]], A4)))>0)=TRUE,Table76[[#Headers],[blue]], "")

<tbody>
</tbody>

Actual Outputs after calculations (Columns B/C/D):

	A	B	C	D	E
1​		color1	color2	color3	Expected Output
2​	text : yellow x	yellow			yellow
3​	text : red yellow	yellow	red		Error
4​	text : empty				No result

<tbody>
</tbody>

Now using the output (Columns B/C/D) from the table above, how do I get Column E values:

1) if the row has one value (i.e. ROW 2 / B2) return that value
2) if the row has two or more values (i.e. ROW3 / C3&D3) return specific text
3) if the row has no values (i.e. ROW4) return specific text (2)

Appreciate your help!

 

[Peter_SSs]

I am using ... formulas to get the outputs (yellow/red/...) in Columns B/C/D

OK, that does make a difference.

So now the question is what version of Excel are you using? Do you have the CONCAT function?

E2: =IF(COUNTIF(B2:D2,"?*")=0,"No results",IF(COUNTIF(B2:D2,"?*")=1,CONCAT(B2:D2),"Error"))

 

[Dervos]

I am using Office 365 and the CONCAT function is working perfectly!

Thank you!!!

 

[Peter_SSs]

I am using Office 365 and the CONCAT function is working perfectly!

Thank you!!!

Ah, great!
You're welcome.