+ Reply to Thread
Results 1 to 7 of 7

Function to match text and return number in adjacent cell?

  1. #1
    Registered User
    Join Date
    06-18-2008
    Posts
    4

    Function to match text and return number in adjacent cell?

    Hi, I'm trying to conjure up the easiest way to have some sort of function that will locate a text from column A in column B, and then return the value that is adjacent to that cell, in column C.

    i.e:

    (a) (b) (c) (d)
    1) A B 2 =findzor(a1,b:b)
    2) B D 4
    3) C A 5
    4) D C 9

    so that the function finds the text in a1 in the b column and returns the value adjacent to the right, which would be 5.

    tried using offset and match and it turns out i'm over my head

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Function to match text and return number in adjacent cell?

    looks a bit oversimplified have you a real sample of what you are doing?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    06-18-2008
    Posts
    4

    Re: Function to match text and return number in adjacent cell?

    Alright, its basically like this: I've got a list of team names in column A. Column B will be their total points scored, which is what I need the function for. In column C I have their first round matchups, and in column D, I have the scores to the games. This continues in this fashion for 8 rounds, so column E would be the second round matchups and column F the scores and so on. I would like to be able figure out the total number of points scored by the team without going through and just adding it up for all the teams, so I need a function that matches the team name in column A to its place in column C, then returns the score in the cell to the right. These scores would be summed to give the total.

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Function to match text and return number in adjacent cell?

    try the sumif functoin
    failing that post a sample workbook, these team thing questions have a habit of growing more and more complex unless its looked at from the begining

  5. #5
    Registered User
    Join Date
    06-18-2008
    Posts
    4

    Re: Function to match text and return number in adjacent cell?

    Okay, here's an example workbook for what I'm trying to do.
    Attached Files Attached Files

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Function to match text and return number in adjacent cell?

    Use VLOOKUP.

    In C2: =VLOOKUP($A2, $H$2:$I$9, 2, 0)
    ...copied down.

    In D2: =VLOOKUP($A2, $J$2:$K$9, 2, 0)
    ...copied down.

    Just keep adjusting the two-column range as you move across the chart..
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  7. #7
    Registered User
    Join Date
    12-15-2014
    Location
    Salem, MA
    MS-Off Ver
    2010
    Posts
    1

    Re: Function to match text and return number in adjacent cell?

    Thank you! I spent all day yesterday trying to figure this out...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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