+ Reply to Thread
Results 1 to 9 of 9

If A Range Contains Specific Text, Return Adjacent Cell Value

  1. #1
    Registered User
    Join Date
    08-10-2017
    Location
    Lexington, Kentucky
    MS-Off Ver
    2013
    Posts
    5

    If A Range Contains Specific Text, Return Adjacent Cell Value

    So I have this data set of locations and their net sales that I download as an excel file, but it labels the location names in an unfavorable way. I want to be able to paste the data set that I downloaded into a "box" and then my other "boxes" would fill automatically to the corresponding location name.

    I made an example of what I'm doing.
    Booth 1 = MLocation-B1
    I want to be able to paste the "MLocations-B#" into the box and the formula would find the MLocation-B1 and then return the Net Sales adjacent to it into the "Booth 1 Net Sales"

    I want to do this weekly so it would be great if I was just able to download it and then paste it.

    Thank you!


    HELP-IfRange.PNG

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: If A Range Contains Specific Text, Return Adjacent Cell Value

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: If A Range Contains Specific Text, Return Adjacent Cell Value

    Hi Wallace and welcome to the forum,

    Why don't you put this formula in I3 and pull it down. I think this is what you are really looking for.

    =TRIM(MID(G3,1+FIND("-",G3),5))

    If not then attach a workbook and tell us exactly what you need to find.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    08-10-2017
    Location
    Lexington, Kentucky
    MS-Off Ver
    2013
    Posts
    5

    Re: If A Range Contains Specific Text, Return Adjacent Cell Value

    Thanks for responding! The Net Sales from the boxes on the right need to coincide with the Net Sales in the boxes on the left with the matching Location Data.

    Sheet 1 is what I have
    Sheet 2 is what I want

    Thank you!

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: If A Range Contains Specific Text, Return Adjacent Cell Value

    Did you try Marvin's suggestion?

  6. #6
    Registered User
    Join Date
    08-10-2017
    Location
    Lexington, Kentucky
    MS-Off Ver
    2013
    Posts
    5

    Re: If A Range Contains Specific Text, Return Adjacent Cell Value

    This is all it doesthis is all it does.PNG
    Last edited by Wallace113; 08-10-2017 at 11:47 AM.

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: If A Range Contains Specific Text, Return Adjacent Cell Value

    Hi Wallace,

    Your example in post #6 isn't like what you gave us in your first post. See the attached to show you what I think you are really looking for. Did you give us the wrong file?

    Which Booth is Best Pivot.xlsx

  8. #8
    Registered User
    Join Date
    08-10-2017
    Location
    Lexington, Kentucky
    MS-Off Ver
    2013
    Posts
    5

    Re: If A Range Contains Specific Text, Return Adjacent Cell Value

    Sorry! Wrong file was attached!

    This is all it does: this is all it does.PNG

  9. #9
    Registered User
    Join Date
    08-10-2017
    Location
    Lexington, Kentucky
    MS-Off Ver
    2013
    Posts
    5

    Re: If A Range Contains Specific Text, Return Adjacent Cell Value

    Marvin,

    I'll have to try it out tomorrow, but I think it might be the solution! I'll let you know!

+ 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. Find specific value in Row, Return Text from Adjacent Cell
    By oneforfive in forum Excel General
    Replies: 5
    Last Post: 07-21-2015, 03:57 PM
  2. Replies: 2
    Last Post: 09-07-2013, 07:50 PM
  3. [SOLVED] Formula to find a specific text within a range and return the value in the next cell
    By 00pumpkin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-19-2013, 02:11 PM
  4. Search for specific value in a row, then return content og adjacent cell
    By linaudio in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-20-2013, 09:11 AM
  5. [SOLVED] Return contents of a cell that contains a specific text within a range.
    By nhi in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-04-2013, 07:54 PM
  6. [SOLVED] Find text in a column or range and return value of adjacent cell
    By chemoul in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-20-2012, 04:30 AM
  7. [SOLVED] Find Specific Text in Text String and Return Value in Adjacent Column
    By watchouse in forum Excel General
    Replies: 2
    Last Post: 07-11-2012, 03:53 PM

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