Results 1 to 4 of 4

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

  1. #1
    New Member jsuvman58's Avatar
    Join Date
    Jan 2012
    Location
    McKinney, TX
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Legend VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Get values to the left of VLOOKUP.

    You need INDEX/MATCH like this

    Sheet2

     ABCDEF
    1Department Employee ID Name Address Sales
    2Sales 34Jim KimAnywhere  
    3Production 45John DoeAnywhere  
    4Accounting 32Jane HattyAnywhere  
    5Operations 67Joe TurnerAnywhere  

    Spreadsheet Formulas
    CellFormula
    F1=INDEX(A1:A5,MATCH("Jim Kim",C1:C5,0))


    Excel tables to the web >> Excel Jeanie HTML 4
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default 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
    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

  4. #4
    New Member jsuvman58's Avatar
    Join Date
    Jan 2012
    Location
    McKinney, TX
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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