+ Reply to Thread
Results 1 to 6 of 6

Concatenate and add formula

  1. #1
    Forum Contributor
    Join Date
    01-18-2013
    Location
    England
    MS-Off Ver
    2016 (Mac)
    Posts
    149

    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*

    Thank you in advance for your help
    Attached Files Attached Files

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    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)"})
    HTH
    Regards, Jeff

  3. #3
    Forum Contributor
    Join Date
    01-18-2013
    Location
    England
    MS-Off Ver
    2016 (Mac)
    Posts
    149

    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. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Concatenate and add formula

    Hi Margate,

    That's not the results I'm getting.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    01-18-2013
    Location
    England
    MS-Off Ver
    2016 (Mac)
    Posts
    149

    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. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    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)

    Source: https://support.office.com/en-us/art...rs=en-US&ad=US

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Drag CONCATENATE Formula or other formula till last cell in the spreadsheet
    By E5254730 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-23-2017, 10:51 AM
  2. Replies: 9
    Last Post: 11-19-2014, 04:15 PM
  3. Replies: 2
    Last Post: 08-24-2014, 04:56 AM
  4. Replies: 2
    Last Post: 04-12-2010, 12:35 PM
  5. Replies: 1
    Last Post: 05-20-2009, 08:15 AM
  6. Replies: 1
    Last Post: 05-20-2009, 07:56 AM
  7. Evaluating results of a concatenate formula, as a formula
    By dodger in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-09-2005, 09:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1