Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Feb 2010
    Location
    Essex, UK
    Posts
    48
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Question Problem extracting data from a table when you don't know where the lookup value is

    Hi.

    I've written many a complicated lookup function in Excel, but the seemingly simple task I am trying to do now has left me stumped.

    Attached you will find a workbook. It has two sheets - the first lists a set of public transport services and has a code for the service in column A. The second has a list of composite timetables - multiple services on one timetables - with an ID for the composite in column A again, and a set of values in column E onwards referring back to the individual Service IDs, one cell per service. It is done this way on purpose because the service codes have to be correctly formatted (correct number of spaces) so they are all validation drop-downs with the Sheet 1 service IDs as the validation range.

    I now want to insert a column on the first sheet which shows what composite a service ID has been attached to. But that's the bit that fails. I can look up on a given row, or in a given column, but I want to look up anywhere on the sheet and return the composite ID from column A. Ideally I even want to extend the lookup range, as users will be able to add another composite line. But that's for the future - I can't even get it to work on the table I've got!

    I don't care about errors where a service ID occurs more than once in different composites - returning just one ID is fine.

    Also, it has to be worksheet functions. This will go to a number of users in different areas who will all have different macro pollicies, and is in any case created by code in the first place - so no macro solutions.

    Thanks!
    Stuart
    Attached Files Attached Files

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Stuart, I'm sure you can do what you want with a FIND or FINDNEXT macro. You have provided a sheet but no examples of the end desire.

    Are you saying you want to look in all other sheets for the id number in col B and when found put in col c. If so, what if more than one?, If not so, EXPLAIN.

  3. #3
    Lounger
    Join Date
    Feb 2010
    Location
    Essex, UK
    Posts
    48
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I did explain, but happy to make it clearer.

    On the attached workbook, look at the "Services" sheet. Cell A18 has the entry "AV 50 "
    Now look at the "Composites" sheet. The reference "AV 50 " appears in E14, and the ID attached to that row, in A14, is Z284. I want to write that value onto line 18 of the Services sheet, say at N18 not that that matters.

    So, to summarise, I want to look up a value from Services!A18 in Composites!E4:O26 and have Composites!A14 returned into Services!N18

    And I want to do the equivalent lookup for each value in Services!A4:A155, writing the answer in Services!N4:N155.

    Finally, as I said in my original post, it must NOT be a macro.

  4. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Copy this to your n1>change c! to Composites!>copy down

    '=IF(SUM(INDEX(('c'!$A$4:$O$500=A4)*(ROW('c'!$A$4: $O$500)-ROW(A$1)),)),INDEX('c'!$A$2:$A$500,SUM(INDEX(('c'! $A$4:$O$500=A4)*(ROW('c'!$A$4:$O$500)-ROW(B$1)),))),"")

  5. The Following User Says Thank You to Supershoe For This Useful Post:

    sjreynolds143 (2014-09-26)

  6. #5
    Lounger
    Join Date
    Feb 2010
    Location
    Essex, UK
    Posts
    48
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I'm seriously impressed! Works a treat - thanks very much!

    Now I'm going to have to go away and work out HOW it works!

Tags for this Thread

Posting Permissions

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