+ Reply to Thread
Results 1 to 16 of 16

Search for a string, grab the numbers from adjoining column

  1. #1
    Registered User
    Join Date
    03-26-2016
    Location
    London, England
    MS-Off Ver
    Excel Plus 2010
    Posts
    8

    Search for a string, grab the numbers from adjoining column

    Hi,

    I have created this nice worksheet where I add all my costs, and then the numbers gets worked on and it splashes some nice grafs telling me I can't afford splurging more on expensive ice-cream.

    The thing is I would like to have some way to calculate what I've spent this far, and compare it to how much I have left to spend the coming year.
    So what I'm looking for is a way to search a column, find a value, lets say "Bike", then grab the adjoining number. Do this for the column and present me the number.

    In my attached example I would for instance want to search B2:B17 for the string Bike, grab the numbers from the corresponding C-columns, add them and subtract from the value in C25.

    Might be easier said than done?

    Any help much appreciated, Thanks! :)
    Attached Files Attached Files

  2. #2
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: Search for a string, grab the numbers from adjoining column

    Welcome to the forum!

    Try
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If your questions has been answered to your satisfaction please don't forget to do the following:

    Add Reputation ... and ... Mark Thread as Solved

    Thanks,

    Ma 10:8b Freely you have received; freely give.

  3. #3
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: Search for a string, grab the numbers from adjoining column

    In looking at your example you could also do ...
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and fill down to make it automatic, but you would need to change Bike to Bikes in the range B2:B12 so it would find a match, and the same with the other items.

  4. #4
    Registered User
    Join Date
    03-26-2016
    Location
    London, England
    MS-Off Ver
    Excel Plus 2010
    Posts
    8

    Re: Search for a string, grab the numbers from adjoining column

    Thanks, but throws up errors.
    I'm quite new to Excel, might be doing it wrong but copy paste didn't work, heh..

  5. #5
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: Search for a string, grab the numbers from adjoining column

    If you copied the second formula into cell D25 you need to change "Bikes" in Cell C25 to "Bike" and the same with the others C26, C27 etc... To the formula "Bike" and "Bikes" is not a match.

  6. #6
    Registered User
    Join Date
    03-26-2016
    Location
    London, England
    MS-Off Ver
    Excel Plus 2010
    Posts
    8

    Re: Search for a string, grab the numbers from adjoining column

    Thanks,
    I get the same error while trying both of your examples. I get "The formula you typed contains an error". Not very informative...
    ERROR.jpg
    Last edited by noobis; 03-26-2016 at 05:17 PM.

  7. #7
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: Search for a string, grab the numbers from adjoining column

    Strange...this is the formula that works in the test file that you attached. It is working currently with a value of 480.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If this still doesn't work than I'm at a loss as to what the issue is!

  8. #8
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: Search for a string, grab the numbers from adjoining column

    I may know what the issue is...try
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    03-26-2016
    Location
    London, England
    MS-Off Ver
    Excel Plus 2010
    Posts
    8

    Re: Search for a string, grab the numbers from adjoining column

    Same thing. Very strange. Been looking at some different excel-sites and the format you suggest should work I think..
    Is there anything I could be doing wrong here? I'm using Excel 2010, could that make a difference?

  10. #10
    Registered User
    Join Date
    03-26-2016
    Location
    London, England
    MS-Off Ver
    Excel Plus 2010
    Posts
    8

    Re: Search for a string, grab the numbers from adjoining column

    Would you mind adding the formula to my test-file and uploading it? Just to make sure I'm not doing anything crazy here. If it still doesn't run, we can conclude that it's my Excel installation that's weird and not me

  11. #11
    Registered User
    Join Date
    03-26-2016
    Location
    London, England
    MS-Off Ver
    Excel Plus 2010
    Posts
    8

    Re: Search for a string, grab the numbers from adjoining column

    OK, I got it to work. Apparently it's some crazy regional language setting.

    I however have one issue still. The formula only seems to grab the first instance of "Bike" and then stop. When I have several instances of "Bike", only the first will be subtracted from my early budget. I'm attaching an updated test-file. If anyone have any suggestions I would be very happy.

    Thank you!
    Attached Files Attached Files
    Last edited by noobis; 03-28-2016 at 04:37 AM.

  12. #12
    Registered User
    Join Date
    03-26-2016
    Location
    London, England
    MS-Off Ver
    Excel Plus 2010
    Posts
    8

    Re: Search for a string, grab the numbers from adjoining column

    removed for simplicity
    Last edited by noobis; 03-28-2016 at 04:39 AM.

  13. #13
    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,360

    Re: Search for a string, grab the numbers from adjoining column

    Try this:

    =C25-SUMIF(B2:B17;"*bike*";C2:C17)
    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.

  14. #14
    Registered User
    Join Date
    03-26-2016
    Location
    London, England
    MS-Off Ver
    Excel Plus 2010
    Posts
    8

    Re: Search for a string, grab the numbers from adjoining column

    Yej! It works! Thank you soo much Moongose36 and AliGW!

    This is my new fav forum!

  15. #15
    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,360

    Re: Search for a string, grab the numbers from adjoining column

    Glad to help - thanks for the reputation!

  16. #16
    Forum Contributor mongoose36's Avatar
    Join Date
    06-10-2013
    Location
    Lincoln
    MS-Off Ver
    Excel 2013
    Posts
    389

    Re: Search for a string, grab the numbers from adjoining column

    Quote Originally Posted by noobis View Post
    OK, I got it to work. Apparently it's some crazy regional language setting.
    Very strange! I'm glad @AliGW helped you the rest of the way! Thanks for the rep!

+ 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. Replies: 8
    Last Post: 02-17-2016, 01:19 PM
  2. Search column for string and date then search adjacent cell and pop up message
    By ftwobtwo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-06-2014, 09:55 AM
  3. Replies: 1
    Last Post: 09-11-2014, 10:59 AM
  4. Search column for string and return every row with that string in new sheet
    By myers601 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-03-2012, 06:02 PM
  5. [SOLVED] Search for string across header row, then search for another string down found column
    By TucsonJack in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-14-2012, 02:09 PM
  6. Replies: 1
    Last Post: 07-26-2011, 06:48 AM
  7. [SOLVED] Search range of cells, find a value, output adjoining cell. How?
    By nyys in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-05-2006, 09:55 AM

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