Excel Function (or VBA) to check the same value in cells

Question

If I have like

In first table in sheet1 looks like this

A1   B1
aaa- yes
aaa- no
aaa- no
aaa- no
aaa- no
aaa- yes
aaa- no
bbb- no
bbb- no
bbb- no
bbb- no
bbb- no
bbb- no
ccc- no
ccc- no
ccc- yes

and the next Answer sheet2 is like

A1   B1
aaa- Yes
bbb- No
ccc- yes

Can I ask you for function or (VBA Code) that is checking that

if any same name on sheet1 is "yes" the answer in sheet2 is "yes"
esle if it's all no then on sheet2 is "No"

If that sounded confusing please ask.

1. Have you tried anything? 2. What if you have a yes in front of bbb? 3. What if you have no in front of aaa? 4. Which version of Excel are you using? — bonCodigo
i try use my old match+index formula it show #value! in some cell it should show not showing that so i change my method. bbb- is in column A and Yes,No is in Column B i use 2010 — eathapeking

chuff chuff · Accepted Answer · 2013-01-21T03:49:49

If I understand your question correctly, it can be addressed with a formula. The following formula assumes that the Sheet 1 data is in the range A1:B16.

    =IF(ISERROR(MATCH(A1&"yes",Sheet1!$A$1:$A$16&Sheet1!$B$1:$B$16,0)),"no","yes")

This would be the formula for the first entry in Sheet 2, which I assume is in cell A1.

Note that this is an array formula, so it needs to be entered with the Control-Shift-Enter key combination.

Excel Function (or VBA) to check the same value in cells

2 Answers