We help IT Professionals succeed at work.

How can I insert text based on three cell compare?

model_un
model_un asked
on
Hi All,

I need to insert text if the preceding three cells match.

Example:

1st row    =    A   B   C   D
2nd row   =    A   B   C
Result       =    A   B   C    D

If no match result would be    =     A    B    C    XXX
If the first three cells in the second row match the first row, add the fourth cell. If it doesn't match then it should insert a placeholder text (like XXX).

Does this make sense?

Attached is a sample file.

Thanks,

Fernando copy-down.xlsx
Comment
Watch Question

Rob HensonFinance Analyst
BRONZE EXPERT

Commented:
Assuming A1 to D1 and A2 to D2 contain the values, how about:

=IF(AND(A1=A2,B1=B2,C1=C2,D2=""),D1,"XXX")

Thanks
Rob H

Commented:
in cell answer:
=IF(AND(A1=A2, B1=B2, C1=C2), D1,"XXX")
Finance Analyst
BRONZE EXPERT
Commented:
I have now looked at the sample file.

In a column of to the right, put this formula, starting in row 2:

=IF(D2<>"",D2,IF(AND(A1=A2,B1=B2,C1=C2,D2=""),D1,"XXX"))

Copy down the extent of the data. Then copy paste values into column D. This will still leave some blanks but can be repeated until all complete.

Alternatively, apply a filter to the data and select Blanks in column D. Then add this formula to the first blank row, assuming its row 4:

=IF(D4<>"",D4,IF(AND(A3=A4,B3=B4,C3=C4),D3,"XXX"))

This will copy the value from the row above. Now select the blank cells and press Ctrl D to fill down the formula, the hidden cells will not be overwritten.

Thanks
Rob H
Rob HensonFinance Analyst
BRONZE EXPERT

Commented:
Sorry copied whole of previous formula rather than just required part, should only be:

=IF(AND(A3=A4,B3=B4,C3=C4),D3,"XXX"))

The previous one creates a Circular Reference.

Thanks
Rob H
Rob HensonFinance Analyst
BRONZE EXPERT

Commented:
You can then filter column D for XXX to show missing results.

Thanks
Rob H

Author

Commented:
I was on the right track with the "IF" construction.

Using the filter, fill blanks, etc. I was able to complete the list. Given that it has 400K lines of products. I was going to take me a while to do this manually.

Thanks!

Fernando

Explore More ContentExplore courses, solutions, and other research materials related to this topic.