+ Reply to Thread
Results 1 to 5 of 5

LOOKUP Help - specifying result vector

  1. #1
    Registered User
    Join Date
    06-17-2009
    Location
    Iowa
    MS-Off Ver
    Office 2010
    Posts
    5

    LOOKUP Help - specifying result vector

    I'm sure this is easy, but I'm searching and can't find it.

    Column A has a unique number (identifying a customer). Column B has a sales figure. I have 4 sheets in this document. 2008, 2009, 2010, and 2011. The unique numbers in column A are not the same from year to year. There are many that are common to all or some years, but each year has it's own different list.

    For the sheet with 2008, I want to add a row C for 2009, to show the values for the next year (IF the customer was on the next year's list).

    The logic is something like this: If A2 exists anywhere in column A of sheet 2 (the 2009 sheet), return the corresponding value of Row B from sheet 2.

    Using the LOOKUP wizard gets me part of the way - but the result vector is where I'm stuck. How do I specify the source of the return value? If the unique ID number was found in A72 from the second sheet, I want it to return the value from B72.

    I assume I can have it return something like "NA" if the customer is not on the next year's list.

    I hope I'm explaining this coherently.

    Much appreciated!

  2. #2
    Valued Forum Contributor Miraun's Avatar
    Join Date
    04-03-2009
    Location
    New England
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    554

    Re: LOOKUP Help - specifying result vector

    You're so close! You're looking for a VLookup, because you're looking for something in column A, and want to return column B.
    The other type of Lookup is an HLookup, which will search for values in row 1, and return row 2.
    The Lookup function is mainly used for comparing numbers to an array and determining the number... Think of something like a % rate for commissions with different price points.

    So you're looking for Vlookup(A2,Sheet2!A:B,2,False) Or something similar to that for the logic you explained there,
    Going for Guru! Click the Star to the bottom left of this post if I helped!

  3. #3
    Registered User
    Join Date
    06-17-2009
    Location
    Iowa
    MS-Off Ver
    Office 2010
    Posts
    5

    Re: LOOKUP Help - specifying result vector

    Okay - now I'm getting somewhere. However, for the sake of simplifying my question, I omitted the fact that there is irrelevant data in columns B through H, and that the number I want to pull back is actually in Row I.

    In the 2008 sheet, I am entering this formula in row K, and wanting to get the data from Row I in the 2009 sheet. I figured I could wrangle it, but changing the B to an I in your formula oddly doesn't change anything in the result.

  4. #4
    Registered User
    Join Date
    06-17-2009
    Location
    Iowa
    MS-Off Ver
    Office 2010
    Posts
    5

    Re: LOOKUP Help - specifying result vector

    lookup.xlsx

    To make this easier, I have attached a dummy sheet with three rows of data. 2 of the 3 customers are on both lists, one from each is unique.

  5. #5
    Registered User
    Join Date
    06-17-2009
    Location
    Iowa
    MS-Off Ver
    Office 2010
    Posts
    5

    Re: LOOKUP Help - specifying result vector

    I figured it out. Thank you very much, Miraun, for your help. You got me on the right track. This is the formula that worked: =VLOOKUP(A2,'2009'!A:I,9,FALSE)

    I was misunderstanding the use of A:B and the "2" in your example. Makes sense now.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1