Need to search in tables, found to exactly same lines in these two tables.

enter image description here

Between these two tables I have working VLookup search, but need to have condition "AND these columns are also equal on this searched line".

So, in this example I need to show only:




(cause 4|DDDD has no eqivalent in first table)

re-word question for request:

need to compare two tables and found same lines (it is two export tables). I have working Vlookup between these two tables (middle column). But I also need second condition write to this Vlookup example.

first table:

companyA | value1

companyA | value 2

second table (just switched two columns):

value 1 | companyD

value22 | companyA

value1 | companyA

Need to this information:

companyA | value1 -- because this line is same for both tables

  • Dave: Yes, it is possible. Just look for edited question. Jerry – Jerry1 Jan 30 '15 at 12:27
  • Ah, I see. So you're just comparing 2 tables, and if the data is the same (regardless of the column), they are a match. – Dave Jan 30 '15 at 12:37
  • The second example may have confused things more. Sorry. I'm a little lost why the need for the vlookup and not use something like (in the J2 column for example) =IF(AND(A2=F2, B2=E2), "Match") – Dave Jan 30 '15 at 12:43
  • need to use vlookup (or other search function), because one table is for example 550 lines and second 950 lines :( It is two different exports... – Jerry1 Jan 30 '15 at 12:46
  • I'm sorry, yes that should have been obvious. You want to match the 'row' of 2 tables, but you have no idea where the 2 rows will be in relation to each other? Is that right? If so, I can do this very easily in VBa but not sure if that is an option for you? – Dave Jan 30 '15 at 12:47

Your post does specifically say worksheet formula, but I don't know Excel very well. However, as a work around (since you have had no answer yet), I offer you this VBa.

This should get you going. Please create a copy of the file first, as VBa does not have an undo option! Since you mention in your comments you understand some VBa I will hope the below is actually clear and easily editable to suit your needs!

Option Explicit
Sub DoTheThing()

Dim row As Integer
row = 2

Dim innerRow As Integer
innerRow = 2


Do While Range("A" & row).Value <> ""

    Dim company As String
    Dim companyValue As String

    company = Range("A" & row).Value
    companyValue = Range("B" & row).Value

    Do While (Range("D" & innerRow).Value <> "")

        If Range("D" & innerRow).Value = companyValue And Range("E" & innerRow).Value = company Then
            Range("G" & row).Value = Range("G" & row).Value & "Matches row " & innerRow & ". "
            Range("G" & innerRow).Value = Range("G" & innerRow).Value & "Matches row " & row & ". "
            exit do ' THIS IS NOT TESTED
        End If
        innerRow = innerRow + 1
innerRow = 2
row = row + 1

End Sub

Data looked like:

enter image description here

And after VBa runs:

enter image description here

  • works fine, thx. There is only one odd- script is running 6 minutes for 1000 rows... but, works! – Jerry1 Feb 2 '15 at 8:47
  • I update the code. I've not tested it, but since you're certain there will never be duplications, having the new line of code exit do ' THIS IS NOT TESTED should reduce the overall time – Dave Feb 2 '15 at 9:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.