Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Combine Text & VLOOKUP in a single cell

  1. #1
    Board Regular Dougie1's Avatar
    Join Date
    Jul 2007
    Location
    Scotland
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Combine Text & VLOOKUP in a single cell

    Morning all,

    Using Excel 2007.

    Is it possible to combine a text value and a vlookup into a single cell ?

    By way of example what I have at the moment is the text "Batch 1:" in cell A10 and a lookup in cell B10 which pulls through a comment on Batch1 from another source.

    The problem is that cells A1 to A8 contain further text in each cell. Now this text is a lot longer than Batch 1 but it still needs to be seen.

    So what I have is :

    Batch 1: Then the result of my look up further over the sheet than I would like (it amounts to about 30 spaces away).


    So, I was wondering if it is possible to combine the text Batch 1: and the vlookup into the same cell?

    All help appreciated.

    TIA

    D

  2. #2
    Board Regular JazzSP8's Avatar
    Join Date
    Sep 2005
    Location
    Chorley
    Posts
    1,211
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Combine Text & VLOOKUP in a single cell

    Hi Dougie

    Sounds pretty simple

    Something like

    Code:
    ="Batch 1: "&VLOOKUP(...
    Should see you right?
    The harder you try, the dumber you look.


    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

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

    Default Re: Combine Text & VLOOKUP in a single cell

    Yes you can do something like this
    Code:
    ="YOURTEXT"&(vlookupformula)
    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

  4. #4
    Board Regular Dougie1's Avatar
    Join Date
    Jul 2007
    Location
    Scotland
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combine Text & VLOOKUP in a single cell

    Thanks guys. DOH - sometimes things are so simply missed by me.

    I tried ="Batch 1:"+(vlookupformula) & ="Batch 1:",(vlookupformula) but overlooked using the &.

    Thanks again.

    D

  5. #5
    New Member
    Join Date
    Feb 2008
    Location
    Worcester, UK
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combine Text & VLOOKUP in a single cell

    You can also use the slightly less elegant method of CONCATENATE

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

    Default Re: Combine Text & VLOOKUP in a single cell

    Quote Originally Posted by JazzSP8 View Post
    Hi Dougie

    Sounds pretty simple

    Something like

    Code:
    ="Batch 1: "&VLOOKUP(...
    Should see you right?
    First, I would like to apologize for digging up an old thread. I have been struggling getting this to work. My vlookup works fine until I try to add the custom text in front of it as in your example. Is there something I am doing wrong? Format is set to general, and my formula is written as ="Verification Frequency:" & VLOOKUP(A18,'Sheet 1'!A:AE,28,1) which returns a #VALUE ! error. Any thoughts? Thanks in advance

  7. #7
    Board Regular JazzSP8's Avatar
    Join Date
    Sep 2005
    Location
    Chorley
    Posts
    1,211
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Combine Text & VLOOKUP in a single cell

    Blimey, this is an old thread...

    But - Can't see anything wrong with your Formula from that, I think it's a bit odd it's returning a #VALUE error though.

    Just done a quick test with some data and the formula works fine here?

    Does anything on this link help?; https://support.office.com/en-us/art...b-0a11a20e409e
    The harder you try, the dumber you look.


    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,592
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Combine Text & VLOOKUP in a single cell

    At the bottom of the Advanced section of Excel options, do you by any chance have the 'Transition formula evaluation' options checked?

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

    Default Re: Combine Text & VLOOKUP in a single cell

    Quote Originally Posted by RoryA View Post
    At the bottom of the Advanced section of Excel options, do you by any chance have the 'Transition formula evaluation' options checked?
    I do. Is that what I want?

    I have found a workaround for the time being, it just isn't ideal and I am now extremely perplexed as to why I cannot use text and vlookup in a single formula. I looked at it again this morning with fresh eyes to make sure I wasn't missing anything and it does not appear to be so. Thank you all for taking the time to work with me on this.

  10. #10
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,592
    Post Thanks / Like
    Mentioned
    47 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Combine Text & VLOOKUP in a single cell

    No, that's not what you want. If you uncheck it, what error does the cell now show - #N/A?

Some videos you may like

User Tag List

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
  •