using vbLookup with multiple cells?

[Ouka]

Hi all,

So I wrote a really gnarly VB script that pulled data from 26k lines of data and compared this to another worksheet that contained 16k lines of data. The step-wise code:

For x = 1 to 26k
    For y = 1 to 16k
         'comparison code here
    Next y
Next x

understandably took forever and a day to execute. I was told by another Excel user that I should have used a vbLookup function instead, only I have limited experience of single-source-column to single-comparison-column experience with this function (i.e. check values in Col A against values in Col B). How would you write a multi-source to multi-comparison function?


In my particular problem:

In one worksheet, I have data that is in a total of 3 cells in two separate rows.

i.e.

Cells(x, 1).value = "My"
Cells(x, 2).value = "Data"
Cells(x + 1, 1).value = "Values"

On my other worksheet am looking for the same data in:

Cells(y, 1).value
Cells(y, 2).value
Cells(y, 3).value

such that:

If Cells(y, 1).value & cells(y, 2).value & cells(y, 3).value = "MyDataValues" Then
    cells(y, 5).value = "Pass"
End if

How would I do something similar with vbLookup??

Thanks!!

 

[mikerickson]

Do you mean something like

=IF(ISNUMBER(MATCH(Sheet1!A1&Sheet1!A2&Sheet1!B1, Sheet2!A:A, 0)), "pass", "fail")