Match+index with isblank and IFs or what formula to use

[Pätkis]

Hi,

I have a problem with table below. I need to learn what formula to use so that I can get correct answer to Solution X. I added into the table what I need for answer into Solution X column.

User1, Info2-3 varies depending if users fill it or not. Solution X needs to be same in all lines, which have same number than in User1 but it needs to look also if info2 and info3 has text (text is always the same "yes" in info2& info3) and if there is data, Solution X needs to have different value.

I can get this work by using ISBLANK and IFs if the info2 and info3 has the value in the first line when User1 changes. But I cannot get it to work if the first line for info2 and/or info3 is empty but it has something on the second or third line. Can this be resolved somehow using match+index or is there another formulas to use?

[TABLE="width: 500"]
<tbody>[TR]
[TD]User1[/TD]
[TD]Info2[/TD]
[TD]Info3[/TD]
[TD]Solution X[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]yes[/TD]
[TD]1Info3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1Info3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD]1Info3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]2Info2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD]2Info2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]4Info3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]yes[/TD]
[TD]4Info3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]4Info3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]4Info3[/TD]
[/TR]
</tbody>[/TABLE]

 

[silentwolf]

Hi,
if that is all you need then a simple if statement like that would work.

=if(A2="","",if(B2<>"",A2& " "&"info2",if(C2<>"",A2&" " &"Info3",A2)))

or with the value of the cell then you need to change "info2" and "info3" to the according cell reference

HTH

 

[Pätkis]

Hi,

This doesn't work because I won't get same resolution for all cells that have same value in User1.

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl65, width: 64"]User1[/TD]
[TD="class: xl65, width: 64"]Info2[/TD]
[TD="class: xl65, width: 64"]Info3[/TD]
[TD="class: xl65, width: 64"]Solution X[/TD]
[TD="class: xl67, width: 64"]Formula[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, align: right"]1[/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"]yes[/TD]
[TD="class: xl66, width: 64"]1Info3[/TD]
[TD="class: xl67, align: right"]1 Info3[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, align: right"]1[/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"]1Info3[/TD]
[TD="class: xl67, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, align: right"]1[/TD]
[TD="class: xl66, width: 64"]yes[/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"]1Info3[/TD]
[TD="class: xl67, align: right"]1 info2[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, align: right"]2[/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"]2Info2[/TD]
[TD="class: xl67, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, align: right"]2[/TD]
[TD="class: xl66, width: 64"]yes[/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"]2Info2[/TD]
[TD="class: xl67, align: right"]2 info2[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, align: right"]3[/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64, align: right"]3[/TD]
[TD="class: xl67, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, align: right"]3[/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64, align: right"]3[/TD]
[TD="class: xl67, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, align: right"]3[/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64, align: right"]3[/TD]
[TD="class: xl67, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, align: right"]4[/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"]4Info3[/TD]
[TD="class: xl67, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, align: right"]4[/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"]yes[/TD]
[TD="class: xl66, width: 64"]4Info3[/TD]
[TD="class: xl67, align: right"]4 Info3[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, align: right"]4[/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"]4Info3[/TD]
[TD="class: xl67, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, align: right"]4[/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"]4Info3[/TD]
[TD="class: xl67, align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hi,

Based on your inputs in A1:D13 range, you could test following Array Formula in cell D2 :

=IF(MIN(IF($A$2:$A$13=A2,IF($B$2:$C$13="yes",ROW($A$2:$C$13))))=0,A2,INDEX($A$1:$A$13,MIN(IF($A$2:$A$13=A2,IF($B$2:$C$13="yes",ROW($A$2:$C$13)))))&IF(MAX(IF($A$2:$A$13=A2,IF($A$2:$C$13="yes",COLUMN($A$2:$C$13))))=0,"",INDEX($A$1:$C$1,MAX(IF($A$2:$A$13=A2,IF($A$2:$C$13="yes",COLUMN($A$2:$C$13)))))))

Hope this will help

 

[James006]

Hello,

Once you have tested the Formula ... feel free to share your comments ...

 

[Pätkis]

Hi,

It gives me #value ! message when I copy your formula into the table.

 

[James006]

For An Array Formula ....

You do NOT need to use the Enter key ...

You need to use simultaneously the 3 keys : Control Shift Enter

 

[Pätkis]

Thank you it worked! Just one thing I don't understand, if the data table is same where else (for example T9:W25) I cannot get the formula to work... I have checked that I have changed the formulas correctly.

 

[James006]

Glad you have managed to get the array formula working ... :wink: