Matching a query using VLookup from another sheet

I have a sheet here called July 5 2016. It looks like this

Pers.No   Employee/app.name   Date ... etc ... etc  .. Rec.Order
12345      Bob Jones           Jan 2016                EXVM_SI15H55
etc          etc                 etc                     etc

I have another file called Sheet1 which looks like

Description            PEATS  Rec.Order
Helped moving things   284367 EXVM_SI15H55
Database dev           232367 BXVM_SI15H55
 etc                    etc     etc

What I want to do is create another column in July 5 2016 Called Description and PEATS. I want to use VLOOKUP or something similar to take the Rec.Order that is already in the first sheet, and search that Rec.Order in Sheet1, and populate the corresponding PEATS number and Description

How should I go about this? Thanks

Solution 1:

On Worksheet("July 5 2016") you can't use a Vlookup because the information you want is to the left of your index value. Instead use an index/match formula.

For description:

=INDEX(Sheet1!$A$2:$A$100,MATCH($D1,Sheet1!$C$2:$C$100,0))

For PEATS:

=INDEX(Sheet1!$B$2:$B$100,MATCH($D1,Sheet1!$C$2:$C$100,0))

You see, you are matching your lookup value to the corresponding column on Sheet1, which in turn gives you the row it is found on, which selects the data from either column A or B to give you what you want.