Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Lookup Second Occurrence With Vlookup

  1. #1
    Join Date
    29th December 2006
    Posts
    2

    Lookup Second Occurrence With Vlookup

    I am using vlookup to find the ORIGIN data and DESTINATION data but the subheading is the same for both- see below(site name, city...). How do I find the second occurrence for the same heading?
    Is there another command that would be better?

    col A col B
    Line#1ORIGIN INFO:
    Line#1 CONTACT NM
    Line#1 CONTACT PH#
    Line#1 CONTACT EML
    Line#1 SITE NAME
    Line#1 ADDRESS
    Line#1 CITY
    Line#1 ST ZIP CTY
    Line#1 DAY&WINDOW
    Line#1 LIVE/DROP?

    Line#1DESTIN INFO:
    Line#1 CONTACT NM
    Line#1 CONTACT PH#
    Line#1 CONTACT EML
    Line#1 SITE NAME
    Line#1 ADDRESS
    Line#1 CITY
    Line#1 ST ZIP CTY
    Last edited by Dave Hawley; December 29th, 2006 at 14:50.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    17th November 2005
    Location
    North East Pennsylvania, USA
    Posts
    693

    Re: Store Second Occurrence With Vlookup

    The data displayed is a little confusing.

    You could probably use AutoFilter, to see all occurances for one heading.

    If you could post your workbook, or a sample of it, we could better understand what to do.

    Have a great day,
    Stan
    Last edited by Dave Hawley; December 29th, 2006 at 14:50.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,714

    Re: Store Second Occurrence With Vlookup

    Make them 2 seperate tables, or use Pivot Tables

    Or, see lookup nth occurrence or this User Defined Function
    Lookup nth Occurrence/Instance
    Last edited by Dave Hawley; December 29th, 2006 at 14:56.

  4. #4
    Join Date
    29th December 2006
    Posts
    2

    Re: Store Second Occurrence With Vlookup

    Thank You everyone!! the following code worked and I was able to get the data for the same sub-headings under Line#1 ORIGIN INFO & DESTIN INFO (second occurrence) - all headings are in column A and all data in column B-Z. Thanks again!!
    VB:
    Range("A" & WorksheetFunction.Match("Line#1DESTIN INFO:", Range("headings"), 0)).Select '? doesn't work
    DestSite = ActiveCell.Offset(4, 1) 
    If Err Then 'error 1004 occurs and don't know why
        ErrorMessage = "Line#1DESTIN INFO:" 
        Goto ERRORhandling 
    End If 
    DestCity = ActiveCell.Offset(6, 1) 
    If Err Then 
        ErrorMessage = "Line#1DESTIN INFO: City" 
        Goto ERRORhandling 
    End If 
    DestState = ActiveCell.Offset(7, 1) 
    If Err Then 
        ErrorMessage = "Line#1DESTIN INFO: ST ZIP CTY" 
        Goto ERRORhandling 
    End If 
    
    
    Last edited by royUK; December 31st, 2006 at 02:06.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,289

    Re: Lookup Second Occurrence With Vlookup

    I'm glad you solved 7 shared the solution, but please read the Forum Rules and use Code Tags.
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  6. #6
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,347

    Re: Lookup Second Occurrence With Vlookup


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Vlookup To Return Second Occurrence Of String
    By elfudge35 in forum EXCEL HELP
    Replies: 5
    Last Post: January 22nd, 2011, 20:49
  2. Lookup Nth Occurrence
    By dsrt16 in forum EXCEL HELP
    Replies: 17
    Last Post: December 20th, 2008, 10:24
  3. Lookup Nth Occurrence Or Instance
    By Hillel in forum EXCEL HELP
    Replies: 10
    Last Post: August 25th, 2007, 09:51
  4. Return Nth Lookup Occurrence
    By Rweasel6 in forum EXCEL HELP
    Replies: 3
    Last Post: August 16th, 2007, 12:31

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
  •  
porno