Results 1 to 3 of 3

Thread: Data Lookup

  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up

    I know how to do a vertical or horizontal lookup to display data and that's ultimately what I need, but can't seem to get it right. My problem is the source data that I am looking for in an array may or may not be the only thing in the cell I', searching. If this isn't possible, is there at least a way to get the address of the cell where it was found?

    The source (Sheet 1) and target (Sheet 2) data will always be in 2 workbooks and the target will always contain thousands of lines. For brevity sake I've entered just a small excerpt.

    Problem is cell A5 on Sheet 1. It contains 1BLW9C1. Sheet 2 shows 1BLW9C1, 000000 in cell A5. No match is found using either True or False, so it doesn't pick up that data. Also, the data I'm searching for may not always be at the beginning of the cell in Sheet 2. It may be at the end and could be either comma, space or dash delimited.

    Any help greatly appreciated.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See the attached. It will probably be slow for large tables.

    I entered an auxiliary formula in D2:

    =MIN(IF(ISNUMBER(SEARCH(A2,Sheet2!$A$2:$A$8)),ROW( Sheet2!$A$2:$A$8)))

    This is an array formula (confirm with Ctrl+Shift+Enter).
    It returns 0 if the value in A2 is not found on the other sheet, a positive number if it is found.
    The formula for the device name becomes

    =IF(D2>0,INDEX(Sheet2!$B$2:$B$8,Sheet1!D2),"")

    and that for the client

    =IF(D2>0,INDEX(Sheet2!$C$2:$C$8,Sheet1!D2),"")
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Melbourne, Fl USA
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That's amazing - thank you so much! It took me a little bit to sort through it, but I understand the logic and hopefully will be able to apply it myself in the future.

Posting Permissions

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