Announcement

Collapse
No announcement yet.

Lookup on part of cell value

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

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

  • #2
    Re: Lookup on part of cell value

    A simple INDEX/MATCH with wildcards should do it:
    Code:
    =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)))
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

    Comment


    • #3


      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

      Comment

      Working...
      X