using vbLookup with multiple cells?

Ouka

New Member
Joined
Mar 28, 2011
Messages
16
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:

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:

Code:
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!!
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,899
Do you mean something like

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

Watch MrExcel Video

Forum statistics

Threads
1,122,187
Messages
5,594,748
Members
413,930
Latest member
Nela817

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top