# Thread: Get values to the left of VLOOKUP. Thanks: 0 Likes: 0

1. ## Get values to the left of VLOOKUP.

I have a pivot table that can not be changed. I am using VLOOKUP on another sheet to create a different form. Is there a way to get data from the “Left” of the value that the VLOOKUP formula finds?

Here is a example I created. Let’s say VLOOKUP is using the “Employee ID” as its reference. How do I get it to return the “Department”? It would be nice if you could just tell it minus one (-1) and it would work.

I know it can be done with a macro but was wondering if there was an easier way? Maybe with the INDEX or MATCH function? I am not that familiar with these.

=VLOOKUP(34, B2:D5, 3, FALSE) – This would return the address of Jim Kim as you know.
 Department Employee ID Name Address Sales 34 Jim Kim Anywhere Production 45 John Doe Anywhere Accounting 32 Jane Hatty Anywhere Operations 67 Joe Turner Anywhere

2. ## Re: Get values to the left of VLOOKUP.

You need INDEX/MATCH like this

Sheet2

 A B C D E F 1 Department Employee ID Name Address Sales 2 Sales 34 Jim Kim Anywhere 3 Production 45 John Doe Anywhere 4 Accounting 32 Jane Hatty Anywhere 5 Operations 67 Joe Turner Anywhere

 Cell Formula F1 =INDEX(A1:A5,MATCH("Jim Kim",C1:C5,0))

Excel tables to the web >> Excel Jeanie HTML 4

3. ## Re: Get values to the left of VLOOKUP.

I would use INDEX/MATCH as per Vog, but there are ways to actually use VLOOKUP to do this eg:

Richard Schollar: VLOOKUP Left! | VLOOKUP WEEK

4. ## Re: Get values to the left of VLOOKUP.

Thanks VoG. I was able to use a variation of your formula and it worked great. I now understand the Index and Match a little better. I will now take a look at the option that Firefly2012 gave.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•