VBA VLOOKUP Help Please

[twothings]

Hello everyone

I have basic experience with Excel and I have reviewed previous forum posts but can't understand how to adapt vba to my scenario, so seek your assistance please.

Situation
I have a sheet that will be containing a very large amount of data so to assist in reducing file size I am looking for the VBA alternative to having a VLOOKUP prefilling several columns.

This is a working sheet for example to assist my explanation:
http://s000.tinyupload.com/?file_id=39025698863238148574

I have two sheets named Records and Staff.

Staff: Staff ID (Column B2:~), Name (Column C2:~) and Group (Column D2:~).

Records: Where records are added regarding all staff members. When entering the details, the Staff ID number is typed in and the details for name are looked up and represented in the Name column (C7:~) and Group column (D7:~).

I was using vlookup with the following formulas but as stated, when I extend these formulas down the sheet the file size becomes huge and not suitable for my use.

=IFERROR(VLOOKUP(B7,Staff!B2:D27,2,FALSE),"")

&

=IFERROR(VLOOKUP(B7,Staff!B2:D27,3,FALSE),"")

Thanks in advance

 

[Joe4]

When you have two separate tables/recordsets that large, that are related by a common field (or fields), what you are describing is really a relational database; something that Access is much better suited to handle than Excel. So, if at all possible, you may want to look at using Access rather than Excel for this.

 

[twothings]

Hi Joe
Thank you for the suggestion, however I do not have the facility to use Access unfortunately. Im stuck with Excel

 

[Joe4]

I have long heard that INDEX/MATCH is more efficient than using VLOOKUP. So before resorting to VBA, you may want to look at that. The following lins contain details and tutorials on how to use it.

Why INDEX MATCH is Better Than VLOOKUP
http://www.mrexcel.com/articles/excel-vlookup-index-match.php