0

How can I match column A with other C and E (columns are not adjacent to each other) and fill 0 in column G if column A matches either column C or E. If column A does not match neither C or E fill the value of column A in G? Examples:

A    B    C   D   E   F   G   
2    3    4   5   6   7   2
3    4    5   6   7   8   3
4    5    6   7   8   9   0
5    6    7   8   9   10  0
6    7    8   9   10  11  0
Improve this question
2
  • 1
    I fixed the formatting, but your example does not match the description.
    – fixer1234
    Jun 28, 2015 at 3:50
  • Welcome to Super User. Unfortunately, we are not a code-writing service. Instead of simply asking for code to perform a particular task, please show us what you've tried so far (including any code you currently have) and where you're stuck so that we can help you with your specific problem. Questions that only ask for code are too broad and are likely to be put on hold or closed. Please read How do I ask a good question?
    – DavidPostill ♦
    Jun 28, 2015 at 6:06

2 Answers 2

Reset to default
1

The formula for data starting in row 1, according to your description is

=IF(OR(A1=C1,A1=E1),0,A1)

The data in your sample does not match the described results. In the sample, no data in column A matches either C or E, so the result in G is the value from A. This is what the result is with your data sample and your business logic:

enter image description here

Improve this answer
0

If you mean that you want G to be equal to A except if A is in C or E column (regardless of the line) :

Formula to be put in G1 :

=IF(AND(ISERROR(MATCH(A1,C:C,0)),ISERROR(MATCH(A1,E:E,0))),A1,0)

ISERROR(MATCH(A1,C:C,0)) will tell whether A1 is within column C (it will return FALSE if A1 is in C). You want the cell to print 0 except if A is in neither of both columns.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .