Announcement

Collapse
No announcement yet.

Lookup Second Occurrence With Vlookup

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 15:50.

  • #2
    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, 15:50.

    Comment


    • #3
      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, 15:56.

      Comment


      • #4
        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!!
        Code:
        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, 03:06.

        Comment


        • #5
          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

          New users should read the Forum Rules before posting

          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.

          Comment


          • #6


            Re: Lookup Second Occurrence With Vlookup

            See: http://www.ozgrid.com/forum/showthread.php?t=54734
            Kris

            ExcelFox

            Comment

            Working...
            X