Checking if the value in various columns somehow match the value in one specific column

Question

I have a huge database and I want to check if the value (different words) in the range H:V matches what's in column D, just a word would be matched in this case.

Here what it would look like:

Columns    A B C D E F G H I J K L M N O ...
Rows   1   Something           Green   Other 
       2   Something
       3   Something     Green        Blue
       4   Something          Violet
       5   Something
       6   Something       Blue
       7   Something              Dark
       8
       9
       10

Thank you.

Does this need to be row by row (like checking H1 against D1)? — ServerS, Sep 02 '14 at 19:07
That would be great, if possible. I need to check if everything with a value from H1:V1 somehow matches D1 — Beavis855, Sep 02 '14 at 19:10
What kind of output are you looking for, and where does the output go? — ServerS, Sep 02 '14 at 19:14
I just want to know if a word in different rows matches the whole sentence in column D. If it's a formula I'm going to use Column X for my output either as a Yes or No — Beavis855, Sep 02 '14 at 19:15
OK...so then it will be yes only if each and every cell in the range H1:V1 = D1? — ServerS, Sep 02 '14 at 19:16
Yes, that would be perfect because like I said some will be empty so I assume the output will be No. Thanks for helping me out, I'm still getting this whole excel formulas thing. — Beavis855, Sep 02 '14 at 19:18

ServerS · Accepted Answer · 2014-09-02T19:58:41.007

0

You can use =IF(COUNTIF($H1:$V1,$H1)=15,IF($H1=$D1,"Yes","No"),"No"). You can put this in Column X and autofill down. It works by checking if all values in the range H1:V1 are equal. If not, the formula will return "No," while if yes, the formula will check and see if the values equal the value in D1. If so, it returns "Yes." If not, it returns "No."

EDIT:

For checking if any one cell matches, use this: =IF(HLOOKUP($D1,$H1:$V1,1)=$D1,"Yes","No"). It will return #N/A if it's D reference is blank.

edited Sep 02 '14 at 19:58

answered Sep 02 '14 at 19:34

ServerS

452
3
15

That's perfect. Thank you so much. I just have one question, it's not giving me any YES. How do we make it Yes as results even if only 1 word matches what's on column D? – Beavis855 Sep 02 '14 at 19:40
No. There's always something on it. H:V may have a few blank rows but that's ok. – Beavis855 Sep 02 '14 at 19:56

Checking if the value in various columns somehow match the value in one specific column

1 Answers1