VBA alternate to VLOOKUP

[22strider]

Hello Friends

Is there a VBA alternate way of doing what VLOOKUP does in worksheet?

For my project I have to search and grab information from other spreadsheet(s). With size of the data it is taking unacceptably long time to get the information.

If there is a way to perform this action using VBA then my assumption is that it would be faster than VLOOKUP.

Thanks
Rajesh

 

[Rick Rothstein]

Is there a VBA alternate way of doing what VLOOKUP does in worksheet?

For my project I have to search and grab information from other spreadsheet(s). With size of the data it is taking unacceptably long time to get the information.

If there is a way to perform this action using VBA then my assumption is that it would be faster than VLOOKUP.

The WorksheetFunction offers a VLOOKUP method that can be used, although it might be faster to just use the Find method of a Range (I think it could also be done using Evaluate also)... hard to tell you more since you gave us no information about your set up, what you are trying to find, where you want to find it at nor where or how the return value should come from or be used.

 

[pplstuff]

You can use application.worksheetfunction.match() which goes much faster than vlookup.
MATCH vs FIND vs Variant Array VBA Performance Shootout in Excel 2010 « Excel and UDF Performance Stuff

^^^Match is much faster than find too.


Use match to find the rows/columns you're looking for and return the cell value by using sheet1.cells(x,y).value or something similar.

 

[22strider]

Hello Rick

Thanks for your message. I am posting sample data tables and sample result table. I need to create result table by taking inputs from source1 and source2.
Column 'Tool' is common between Source1 and source2. Following are the tables:

Excel 2007
	A	B	C	D	E
2			Source1
3	Manufacturer Part Number	Manufacturer Name	Lifecycle Phase	Tool1	Tool2
4	10164	MODERN	Unassigned	Fair
5	512504	ABC INC	Unassigned	Excellent
6	512505	OPTEX INC	Unassigned	Excellent
7	B0001	CORPORATION	Unassigned	Excellent
8	B0002	CORPORATION	Unassigned	Excellent
9	B0003	CORPORATION	Unassigned	Excellent
10	B0004	CORPORATION	Unassigned	Excellent
11	B0005	CORPORATION	Unassigned	Excellent
12	B0011	HONG KONG	Approved	Excellent
Source1

Excel 2007
	A	B	C	D
2		Source2
3	Part Number	Description	Manufacturers.MPN Type	Tool
4	ABCD	Random description	Tool	10164
5	EFGH	Random description	Tool	512504
6	XYZ	Random description	Tool	512505
7	LMN	Random description	Tool	B0001
8	QRS	Random description	Tool	B0002
9	GHR	Random description	Tool	B0003
10	KHV	Random description	Tool	B0004
11	XHDG	Random description	Tool	B0005
12	BIFK	Random description	Tool	B0011
Source2

Excel 2007
	A	B	C	D	E
2			Result
3	Part Number(From Source2)	Tool (From Source2)	Manufacturer Name (From Source1)	Tool1 (From Source1)	Tool2 (From Source1)
4	ABCD	10164	MODERN	Fair
5	EFGH	512504	ABC INC	Excellent
6	XYZ	512505	OPTEX INC	Excellent
7	LMN	B0001	CORPORATION	Excellent
8	QRS	B0002	CORPORATION	Excellent
9	GHR	B0003	CORPORATION	Excellent
10	KHV	B0004	CORPORATION	Excellent
11	XHDG	B0005	CORPORATION	Excellent
12	BIFK	B0011	HONG KONG	Excellent
13
Result

Thanks for your help
Rajesh

 

[22strider]

bump

Could someone please review my request?

Thanks
Rajesh

 

[Rick Rothstein]

Could someone please review my request?

Assuming the worksheet names for your source data are Source1 and Source2 (and that your data starts on Row 4, headers on Row 3, as you have shown), put these formulas in the indicated cells on your Result sheet and then copy them down...

A4: =IF(Source2!A4="","",Source2!A4)

B4: =IF(Source2!D4="","",Source2!D4)

C4: =IF(B4="","",VLOOKUP(B4,Source1!A4:B12,2,FALSE))

D4: =IF(B4="","",VLOOKUP(B4,Source1!A4:D12,4,FALSE))

E4: =IF(B4="","",VLOOKUP(B4,Source1!A4:E12,5,FALSE))

 

[22strider]

Hello Rick

Thanks for your message; but I am trying not having to use VLOOKUP. I need to do this by using VBA.

The sample data shown is very small but actual data is large with potential of getting even larger. Currently there are about 4500 rows and about 10 columns that need to be created by reading both the source data spreadsheets. I have set up VLOOKUP but it takes a long time to run.

I was trying to do this by VBA assuming it would be faster than VLOOKUP.

Thanks
Rajesh