1. ## Concatenate and add formula

Hello Everyone,

I have some cells that contain data and I have concatenated them. I now need to add a formula to the end but have no idea how to do it!
So here is my string and it works fine:

``Please Login or Register  to view this content.``

I now want to a formula that will check cell I and depending on what is entered (1 of 3 options) (A) , (B) or (C) will be added in brackets to the end of the string.

Example:

Cell C contains: 1 Test Street
Cell G contains: PG / FP / EPC
Cell I contains: £500,000 - £900,000
Cell AQ has the following formula:

``Please Login or Register  to view this content.``

And this produces in cell AQ: 1 Test Avenue - PG / FP / EPC

But I need it to produce: 1 Test Avenue - PG / FP / EPC (B) So it will check cell I and if it contains <£500 it will add (A), if cell I contains £500,000 - £900,000 it will return (B) and if it contains >£900,000 it will return (C) to the end.
Meaning if it was working as I would like the result of cell AQ (using the example above) would = 1 Test Avenue - PG / FP / EPC (B)

I have no idea how to add a formula to the end! I have attached an example*￼

2. ## Re: Concatenate and add formula

Hi Margate,

Try in AQ8 copied down...

=C8&" - "&G8&" "&LOOKUP(I8,{"£500,000 - £900,000","< £500,000","> £900,000"},{"(B)","(A)","(C)"})

3. ## Re: Concatenate and add formula

Thank you for your help, it works but £500,000 - £900,000 is returning as a (C) for some reason when it should be returning a (B)?

4. ## Re: Concatenate and add formula

Hi Margate,

That's not the results I'm getting.

5. ## Re: Concatenate and add formula

No! Your one is working! Must be something my end. I will mark this down as solved.
Thank you very much for taking the time to help me

6. ## Re: Concatenate and add formula

You are very welcome and thanks for the feedback.

Important to note when using the function Lookup.

Important: The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.
=LOOKUP(lookup_value,lookup_vector,result_vector)

There are currently 1 users browsing this thread. (0 members and 1 guests)