vlookup/search/find values within a text cell
Results 1 to 4 of 4

Thread: vlookup/search/find values within a text cell

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default vlookup/search/find values within a text cell

    Hi, Newbie tothe forum and basic Excel user.

    From a text cell (Column A) I am struggling to extract the Cost Centre(Column B) and report the Project Name (Column C).

    In the table below, I need to see if the values in column B "CostCentre" exist in any part of the cells in column A "Cost Code",and if they do then report the text in column C "Project".

    i.e. does "100000" exist in any of the cells in column A, if so report"Project 1" in the relevant cells.

    I don't think a simple vlookup or find/search formula can do this?

    Is there a way it can be done backwards? i.e. vlookup if any of the valueswithin each cell in column A exist in column B, if they do report the correspondingvalue found in column C.

    To be clear columns B and C are related to each other i.e.
    Cost Centre 100000is Project 1.

    Thanks in advance.



    Tom.


    Cost Code Cost Centre Project
    16586/100000/54545
    100000
    Project 1
    100006
    100001
    Project 2
    16586 100002/54547
    100002
    Project 3
    16586/100003/545HB
    100003
    Project 4
    16586/100001/54549
    100004
    Project 5
    16586 :100005/54550
    100005
    Project 6
    16586/100004/54551
    100006
    Project 7
    100008/54552
    100007
    Project 8
    16586/100007/545.53
    100008
    Project 9
    16586 /100009/54554
    100009
    Project 10
    16586/100010/545.55
    100010
    Project 11





  2. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vlookup/search/find values within a text cell

    I can't think of a simple way to do this, particularly as the inputs seem to follow an irregular pattern.

    If you could write some rules to identify the cost centre within the inputs, then you could write a formula to isolate the cost centre from the inputs, and then lookup the cost centre against your reference table.

    A possible rule to do this could be as follows
    1) If the input contains the text string "/5", then take the preceding 6 digits as the cost centre.
    2) If the input does NOT contain the text string "/5", then use the entire input as the cost centre.

    This seems to be right for the sample data you provided, and if so it can be written as a formula.

    QUESTION - is this ruleset correct for ALL your data ?
    If yes, great, we can use that.
    If no, is the amount of further variation small, so that we can adapt the ruleset to deal with one or two more variations ?
    Or is the further variation huge, to the extent that it will not be possible to create a reliable ruleset in this way ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: vlookup/search/find values within a text cell

    Thanks for the reply.

    Unfortunately the variation is huge and un-uniform in its format. Isolating the cost centre is my challenge, which is why I was hoping there was a way to a type of reverse vlookup or find formula.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,841
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: vlookup/search/find values within a text cell

    One option, with a helper column
    ABCDE
    1Cost CodeCost CentreProject
    216586/100000/54545100000Project 116586/100000/54545Project 1
    3100006100001Project 216586/100001/54549Project 7
    416586 100002/54547100002Project 316586 100002/54547Project 3
    516586/100003/545HB100003Project 416586/100003/545HBProject 4
    616586/100001/54549100004Project 516586/100004/54551Project 2
    716586 :100005/54550100005Project 616586 :100005/54550Project 6
    816586/100004/54551100006Project 7100006Project 5
    9100008/54552100007Project 816586/100007/545.53Project 9
    1016586/100007/545.53100008Project 9100008/54552Project 8
    1116586 /100009/54554100009Project 1016586 /100009/54554Project 10
    1216586/100010/545.55100010Project 1116586/100010/545.55Project 11

    Program start



    Worksheet Formulas
    CellFormula
    E2=INDEX($C$2:$C$12,MATCH(A2,$D$2:$D$12,0))

    Array Formulas
    CellFormula
    D2{=INDEX($A$2:$A$12,MATCH("*"&B2&"*",$A$2:$A$12&"",0))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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