Page 1 of 2 12 LastLast
Results 1 to 10 of 15

VLOOKUP Second occurance

This is a discussion on VLOOKUP Second occurance within the Excel Questions forums, part of the Question Forums category; I am trying to find a way to return an second answer for a second occurance using a VLOOKUP function. ...

  1. #1
    New Member
    Join Date
    Jun 2002
    Posts
    3

    Default

    I am trying to find a way to return an second answer for a
    second occurance using a VLOOKUP function. (maybe it can
    be done with something else?) ex. range= A1:C3,
    A1 = 40CA B1 = 30R2 C1 = TRUCK
    A2 = 40CB B2 = 30A1 C2 = CAR
    A3 = 40CA C2 = 30B3 C3 = TRAILER

    VLOOKUP("40CA",A1:C3,3,FALSE) Returns "TRUCK"

    How can I get a formula like this to return "TRAILER" the
    second occurance of "40CA"?

    Thanks, Theo
    .


  2. #2
    New Member
    Join Date
    Jun 2002
    Posts
    18

    Default

    The only suggestion I have is to create a unique ID.

    For example: UNIQUE ID
    Concatenate(A1,B1) = 40CA30R2
    Concatenate(A3,C3) = 40CA30B3

    You need to the same in both sets of data.

    I use this all the time. I not sure if this will work for you.


  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752

    Default

    Why don't you just vlookup column B instead of A?

  4. #4
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Allentown, PA
    Posts
    2,547

    Default

    Perhaps the MyVlookup download will help you with this.

    http://www.thewordexpert.com/downloads.htm
    ~Anne Troy

  5. #5
    Board Regular
    Join Date
    May 2002
    Posts
    63

    Default

    Not the easiest question...

    Answers will depend on whether this is a 'real-time' query, or if you can process the entire data set in one batch.

    If it's real-time, one approach is to insert a new column A, and use Fill to number the records from 1 to the last number. Then sort the entire set of data on Column A Descending. That will reverse the order of your data. Then the VLookup will pick up your second value first.

    If this won't work becase you have more than 2 matches for each value, you could sort the data so that VLookup always gives you the right values. (VBA could be used to help with the sort if it will take processing logic).

    If you can batch-process, there may be better approaches. Depending on your level of comfort with VBA, it isn't difficult to write code to do this.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,859

    Default

    On 2002-06-13 16:13, theob wrote:
    I am trying to find a way to return an second answer for a
    second occurance using a VLOOKUP function. (maybe it can
    be done with something else?) ex. range= A1:C3,
    A1 = 40CA B1 = 30R2 C1 = TRUCK
    A2 = 40CB B2 = 30A1 C2 = CAR
    A3 = 40CA C2 = 30B3 C3 = TRAILER

    VLOOKUP("40CA",A1:C3,3,FALSE) Returns "TRUCK"

    How can I get a formula like this to return "TRAILER" the
    second occurance of "40CA"?

    Thanks, Theo
    See the figure.

    Microsoft Excel - aaRetrieveMultValAssocWithKey theob.xls___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
    =

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    40CA30R2TRUCK






    2
    40CB30A1CAR
    540CATRUCK TRAILER HELI
    3
    40CA30B3TRAILER






    4
    50CA25F5HELI






    5
    40CA20P3HELI






    Sheet1

    To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

    The above image was automatically generated by [HtmlMaker 2.14] If you want this FREE SOFT, click here to download
    This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


    The relevant formulas are:

    E2:

    =MATCH(REPT("z",40),A:A)-(CELL("Row",A1)-1)

    F2:

    The lookup value (Key) of interest.

    G2:

    =IF(LEN($F$2),VLOOKUP($F$2,OFFSET($A$1,0,0,$E$2,3),3,0),"")

    H2:

    =IF(COUNTIF(OFFSET($A$1,0,0,$E$2,1),$F$2)>COUNTA($G$2:G$2),
    INDEX(OFFSET($C$1,MATCH(G2,OFFSET($C$1,0,0,$E$2,1),0),0,$E$2,1),
    MATCH($F$2,OFFSET($A$1,MATCH(G2,OFFSET($C$1,0,0,$E$2,1),0),0,$E$2,1),0)),"")

    which is copied up to J2.

    Aladin



    [ This Message was edited by: Aladin Akyurek on 2002-06-14 23:46 ]

  7. #7
    Board Regular
    Join Date
    May 2002
    Location
    Manchester, England
    Posts
    73

    Default Re: VLOOKUP Second occurance

    Can anyone please explain this - i have the following set of data in column A and need to 'lookup' named ranges from a different spreadsheet for values going into column B

    Sales
    Truck - Counter
    Service
    Dealer
    VCM
    Contract
    Warranty
    Internal

    Total Truck Sales
    Bus - Counter
    Service
    Dealer
    VCM
    Contract
    Warranty
    Internal

    Total Bus Sales

    Total Sales
    Gross Profit
    Truck - Counter
    Truck - Service
    Dealer
    VCM
    Contract
    Warranty
    Internal

    Total Truck Gross Profit
    Bus - Counter
    Service
    Dealer
    VCM
    Contract
    Warranty
    Internal

    Total Bus Gross Profit
    Other Income(Expenditure)
    Price Variance
    Surcharges

    Total Gross Profit
    Total Department Expenses

    Net Profit Contribution



    As you can see, for example, VCM occurs 4 times - is this possible?

    NB there are about 10 of these datasets on the same sheet (dataset being the detail in italics)
    Last edited by Bubba; Mar 9th, 2009 at 12:01 PM. Reason: additional info

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,859

    Default Re: VLOOKUP Second occurance

    Quote Originally Posted by Bubba View Post
    Can anyone please explain this - i have the following set of data in column A and need to 'lookup' named ranges from a different spreadsheet for values going into column B

    Sales
    Truck - Counter
    Service
    Dealer
    VCM
    Contract
    Warranty
    Internal

    Total Truck Sales
    Bus - Counter
    Service
    Dealer
    VCM
    Contract
    Warranty
    Internal

    Total Bus Sales

    Total Sales
    Gross Profit
    Truck - Counter
    Truck - Service
    Dealer
    VCM
    Contract
    Warranty
    Internal

    Total Truck Gross Profit
    Bus - Counter
    Service
    Dealer
    VCM
    Contract
    Warranty
    Internal

    Total Bus Gross Profit
    Other Income(Expenditure)
    Price Variance
    Surcharges

    Total Gross Profit
    Total Department Expenses

    Net Profit Contribution


    As you can see, for example, VCM occurs 4 times - is this possible?

    NB there are about 10 of these datasets on the same sheet (dataset being the detail in italics)
    Is this what you are looking for?

    Vlookup - Return All matches

  9. #9
    Board Regular
    Join Date
    May 2002
    Location
    Manchester, England
    Posts
    73

    Default Re: VLOOKUP Second occurance

    Not really.

    To elaborate, columns B,C and D contain Actual, Budget and Last Year data
    There are 10 regions in the company and each region is on Sheet 1, one below the other - they all contain pretty much the rows I listed above.

    In it's current state, the report is unworkable and not user-friendly... I'm creating a report with each Region on a separate Sheet in another spreadsheet, let's call it Book2.xls

    The report will download in a similar fashion each time it is run, only the data and time frame will change although the number of rows may differ, hence the need for a lookup of some sort.

    On Book2.xls will be 10 sheets... the aim was to have all data that is not zero showing. If the revenue streams in Column A had unique names it would be straightforward then it would be fine but in it's current state, the same reference is repeated up to 4 times in each region so ideally i would need a lookup which says something like... for the first time you find the term "Service" display x, for the second, display y etc etc

    Does this make sense???

    Would really appreciate any pointers as i can't think of another way around this due to the format of the original report - am happy to introduce Names but don't want to cut and paste the original download if poss

    Thanks!

  10. #10
    Board Regular hardeep.kanwar's Avatar
    Join Date
    Aug 2008
    Posts
    655

    Default Re: VLOOKUP Second occurance

    Hi! Anne

    Your Link is not Working

    Hardeep kanwar

Page 1 of 2 12 LastLast

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
  •  


DMCA.com