One VLOOKUP to return all data - is this even possible?

[MelissaGregory]

Hi folks:

I have a workbook with 2 worksheets: "Name" and "Record" that share a common key, which is the "ID" number of the student.

I want to be able to perform a Vlookup on the "ID" field in the "Record" worksheet against the "Name" worksheet.

Objective: Perform a SINGLE Vlookup (on the ID field) and return the values in the 4 columns from the "Record" worksheet and populate it into the "Name" worksheet. I don't want to have 4 separate Vlookups for each column in the "Record" worksheet to get the 4 separate data values - so just one Vlookup on ID field returning and populating the data into their respective columns in "Record" sheet.

I've attached the worksheet here

As well here's the layout:

"Name" worksheet:

Col A = Student ID
Col B= Name
Col C = 2010
Col D = 2011
Col E = 2012
Col F = 2013

"Record" worksheet is the same as "Name" but only has data in Student ID


Thanks!

[Pepe Le Mokko]

On the " record sheet" row 2 sellect range B2: E2.

Enter

Please Login or Register  to view this content.

in the formula bar and commit with Ctrl+Shift+Enter

Pull down as needed

[kvsrinivasamurthy]

Try this array formula in B2 of "Record" sheet and drag across.

Please Login or Register  to view this content.

[MelissaGregory]

Thanks Pepe - worked like a charm! Appreciate it much

Hmmm, I wonder how I'd program this in VBA. Maybe I should repost in VBA section <ponders>