Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Lookup on part of cell value

  1. #1
    Join Date
    23rd November 2005
    Posts
    11

    Lookup on part of cell value

    I have a question regarding searching in cells for a value, and returning corresponding data.

    This is what my workbook looks like:

    Sheet1, cell A1 contains value "D600"
    Sheet1, cell A2 contains value "V-1234"
    Sheet1, cell A3 contains value "DB23"

    Sheet1, cell B1 empty
    Sheet1, cell B2 empty
    Sheet1, cell B3 empty
    ...

    Sheet2, cell B1 contains value "whatever A900"
    Sheet2, cell B2 contains value "i was there D600 till then"
    Sheet2, cell B3 contains value "ok SSSF, V-1234, whatever"

    Sheet2, cell A1 contains value "S0005050"
    Sheet2, cell A2 contains value "S0004321"
    Sheet2, cell A3 contains value "S0001234"

    This is what i'm trying to accomplish:

    1. search each cell value form Sheet1 column A, in Sheet2 column B
    2. when a match is found, return the corresponding value of column A from Sheet2

    So with the above example, the returned value for
    Sheet1, cell B1, would be "S0004321"
    Sheet1, cell B2, would be "S0001234"
    Sheet1, cell B3, would be empty

    Actually in a few words, i need nothing more then the vlookup function, but instead of matching cells on their full cell value, i need to match on part of the cell value.

    I hope i didn't make this to difficult.

    Is there somebody who knows a solution? I've been searching the net but i couldn't find anything that worked out for me.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    2nd March 2006
    Location
    London
    Posts
    988

    Re: Lookup on part of cell value

    A simple INDEX/MATCH with wildcards should do it:
    VB:
    =If(ISNA(MATCH("*"&Sheet1!A1&"*",Sheet2!$B$1:$B$3,0)),"",INDEX(Sheet2!$A$1:$A$3,MATCH("*"&Sheet1!A1&"*",Sheet2!$B$1:$B$3,0))) 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    23rd November 2005
    Posts
    11

    Re: Lookup on part of cell value

    GlennUK,

    You have no idea how much time i already spent in trying to find a solution.
    It's imazing how quick you could help me out.
    However easy this might be for you, for me the code is like chinese. In short, i'm stunned.

    Thank you very very much really

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Part Cell Match Lookup And Display In Part
    By showdj in forum EXCEL HELP
    Replies: 2
    Last Post: March 14th, 2008, 23:15
  2. Replies: 14
    Last Post: February 26th, 2008, 00:27
  3. Lookup Part Text
    By opeyemi1 in forum EXCEL HELP
    Replies: 4
    Last Post: October 12th, 2007, 14:32
  4. Lookup Part Of A Cell
    By dugong in forum EXCEL HELP
    Replies: 24
    Last Post: February 20th, 2007, 05:51
  5. VLOOKUP to lookup part of the text in a cell
    By cjh1984 in forum EXCEL HELP
    Replies: 15
    Last Post: June 3rd, 2004, 01:18

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