Combine Text & VLOOKUP in a single cell

[Dougie1]

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

 

[JazzSP8]

Hi Dougie

Sounds pretty simple

Something like

="Batch 1: "&VLOOKUP(...

Should see you right?

 

[Gerald Higgins]

Yes you can do something like this

="YOURTEXT"&(vlookupformula)

 

[Dougie1]

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]

You can also use the slightly less elegant method of CONCATENATE

 

[dedonx]

Hi Dougie

Sounds pretty simple

Something like

="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

 

[JazzSP8]

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/ar...ue-error-15e1b616-fbf2-4147-9c0b-0a11a20e409e

 

[RoryA]

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

 

[dedonx]

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]

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