+ Reply to Thread
Results 1 to 2 of 2

trouble returning text values using IF and arrays

  1. 03-24-2005, 09:47 AM #1
    speakeasy78 is offline
    Registered User
    Join Date
    03-24-2005
    Posts
    1

    trouble returning text values using IF and arrays

    I'm just beginning to learn advanced points of Excel and wanted to enlist your help. For the life of me I can't figure out how to return a text cell using an IF statement, like the old Basic days of IF, THEN.

    For example, I'm building a baseball lineup card than has multiple listings of players. E.g.: Smith, John 3B, Jones, Josh SS, Van Hausen, Mark CF, etc. The positions "3B", "SS" and "CF" are in different cells than the names "Smith, John", "Jones, Josh" and "Van Hausen, Mark". On the same lineup card spreadsheet, I have the defenses laid out and want to return the corresponding name up to the comma only at 3B. I haven't had any luck getting any name to return by using arrays and the IF function.

    So, again, my goal is to have "Smith" yielded next to my "3B" cell, "Jones" next to my "SS" cell and "Van Hausen" next to my "CF" cell. Any thoughts?
    Reply With QuoteRegister To Reply
  2. 03-24-2005, 11:31 AM #2
    swatsp0p is offline
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    What you really need to use is VLOOKUP, LEFT and FIND. Use an IF and ISERROR only for error trapping (if a position or name is blank).

    You need to set up your roster/position with the position column BEFORE the names (that is, for example positions in column B, names in column C). Sort this range Ascending by position then Name this Range "names".

    On your Lineup card, enter this formula where you want the names to appear (this assumes you begin in H1 with positions listed in G1 and down). Copy this formula down your range...

    =IF(ISERROR(LEFT(VLOOKUP(G1,names,2,0),FIND(",",VLOOKUP(G1,names,2,0))-1)),"None",LEFT(VLOOKUP(G1,names,2,0),FIND(",",VLOOKUP(G1,names,2,0))-1))

    Basically, this formula reads the position in your Lineup card and looks in the range "names" for that value and returns everything in the 2nd column up to the comma. If either the position is blank or the corresponding name field is blank, "None" is returned. The zero in the VLOOKUP forces an exact match of position is found or "None" is returned.

    NOTE: I use a named range, as you then don't have to be concerned with what sheet the range is located in or the specific cells it encompasses.

    Good Luck (and Go Twins!!)

    Bruce
    Bruce
    The older I get, the better I used to be.
    USA
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1