• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Match every single value of range to another range and give "True" / "False"

kuldeepjainesl

kuldeepjainesl

Member
Hello All,

i am searching a way to check whether my Range values (D1:D25) are matching to the values available in same row for range A1:C100

Requirement is to pick each value from D1:D25 and check it in A1:C1 and if any value get match, it should give "True" in coloum "E" and this is to be done for for Each Row.

I am ok with both the potion "Formula or VBA".

Below mentioned piece i found on internet does the job but it provide the answer in multiple coloum due to this statement
Code: 
ThisCell1.Offset(, 1).Value = "TRUE"
.i need all the True in coloum E

Code: 
Dim ThisCell1 As Range
Dim ThisCell2 As Range
    For Each ThisCell1 In Range("A1:C100")
    'This is the range of cells to check
        For Each ThisCell2 In Range("D1:D25")
        'This is the range of cells to compare
            If ThisCell1.Value = ThisCell2.Value Then
                ThisCell1.Offset(, 1).Value = "TRUE"
                Exit For
                End If
            Next ThisCell2
        Next ThisCell1
 
Hi Kuldeep,

Check the file you had uploaded, I don't thing it is in correct format as it is not matching with your description in post #1.

Regards,
 
Hello Somendra, Oh Yes, i was trying to achieve the objective so was doing some trial with this file.

Please note the correction : in this file value to be checked are in E coloum and the coloum being checked as A and B. currently the result are being populated in C and D while the requirement is to have single output coloum. you please ignore the coloum G for now
 
Try below formula in C1 and copy down:

=IF(MAX(IFERROR(MATCH(A1:B1,TRANSPOSE($E$1:$E$3),0),0)),TRUE,"")

Note this is an array formula, so must be entered with Ctrl+Shift+Enter.

Regards,
 
Yes, That also work and this is too an array formula. VBA was not a must but would like to have it only if this is not more than few minute for you because the objective has meet but having VBA can save me in future for large data set as array become slow on large database
 
Hello Kuldeep,

You are comparing two single cell to a range. So you can simply use two MATCH formula along with COUNT.

=IF(COUNT(MATCH(A1,E$1:E$3,0),MATCH(B1,E$1:E$3,0)),TRUE,"")

So this will avoid Array & will be much faster, as lookup_value is a single cell.
 
Thanks Haseeb, This Worked.....:awesome: interesting approach.:) You always come with a different kind of approach for solution. This will be better choice if the cell to be compare is less (Like in my case ). As soon as these will increase, Somendra solution will take the edge.
 
OK Kuldeep,

If your cells are increasing, you still can use with COUNT with CSE,

=IF(COUNT(MATCH(A1:B1,E$1:E$3,0)),TRUE,"")
 
You must log in or register to reply here.
Back
Top