Excel comparing value from row to different columns

Question

I have a Table like this in Sheet1

   A          B
1234.jpg |  c1
1234.jpg |  c2
1234.jpg |  c3
3456.jpg |  c8
3456.jpg |  c9
3456.jpg |  c10
haha.jpg |  c2
haha.jpg |  c5
haha.jpg |  c9

I need the to match the data according to the Columns in Sheet2 and the data should result something like this.

            c1  c2  c3  c4  c5
123.jpg     Y   Y   Y   N   N
3456.jpg    N   N   N   N   N
haha.jpg    N   Y   N   N   Y

I am currently only able to make out this

=IF(ISERROR(MATCH(A2,Sheet1!$A$1:$B$9,0)),"Y","N")

Which returns Y as long as A2 matches something from the array. How do I go about matching it as the Column in Sheet2? I'm open to using functions or VBA

Harun24hr · Accepted Answer · 2017-11-20 04:19:03Z

2

Use following formula to D3 cell as per screenshot.

=IF(SUMPRODUCT(($A$2:$A$10=$C3)*($B$2:$B$10=D$2))=1,"Y","N")

.......................................................................................................................................................
You can also use this array formula.

=IF(ISNUMBER(MATCH($C3&D$2,$A$2:$A$10&$B$2:$B$10,0)),"Y","N")

Press CTRL+SHIFT+ENTER to evaluate the formula as it is an array formula.

After entering formula as array formula, drag and drop to right and down as you need.

edited Nov 20, 2017 at 4:19

answered Nov 20, 2017 at 3:58

Harun24hr

38k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1996384 Over a year ago

I realized that if there are 2 same values in column B for one jpg it would show as N instead. Anyway to resolve that?

Collectives™ on Stack Overflow

Excel comparing value from row to different columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related