Combine Text & VLOOKUP in a single cell

Dougie1

Board Regular
Joined
Jul 27, 2007
Messages
212
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,218
Hi Dougie

Sounds pretty simple :)

Something like

Code:
="Batch 1: "&VLOOKUP(...
Should see you right? :)
 

Dougie1

Board Regular
Joined
Jul 27, 2007
Messages
212
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
 

anonymoose

New Member
Joined
Feb 14, 2008
Messages
49
You can also use the slightly less elegant method of CONCATENATE :)
 

dedonx

New Member
Joined
Jan 31, 2019
Messages
3
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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,796
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
At the bottom of the Advanced section of Excel options, do you by any chance have the 'Transition formula evaluation' options checked?
 

dedonx

New Member
Joined
Jan 31, 2019
Messages
3
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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,796
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
No, that's not what you want. If you uncheck it, what error does the cell now show - #N/A?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,596
Messages
5,445,401
Members
405,331
Latest member
hiccup2020

This Week's Hot Topics

Top