General discussion

  • Creator
    Topic
  • #2130529

    Excel Formula

    Locked

    by cyndi ·

    Please help. I went to an Excel Seminar and came away with one question answered. I need to know how: If I type text in one field the text that “matches” it “pop” into the appropriate fields in the same line.
    example:
    1 2
    Womens Group (I type) STC08 (automatic)
    I am an Excel newB.

All Comments

  • Author
    Replies
    • #3545703

      Excel Formula

      by jimbb ·

      In reply to Excel Formula

      Try this:
      =IF(A1=”Womens Group”;”STC08″;””)
      If you fill in Cell A1, you’ll get this in the cell where the formula is. The last bit (“”, or nothing) is what is shown if the strings don’t match. By the way, my list separator is “;”, you may have to replace them by “,” for you.

      Wouldn’t it be better to use a small table, where a string like “Womens Group” would get you “STC08”, and another string would get another code? Look into the VLOOKUP or HLOOKUP formula for that.

      Jim

      • #3660065

        Excel Formula

        by cyndi ·

        In reply to Excel Formula

        The question was auto-closed by TechRepublic

    • #3667053

      Excel Formula

      by l_lk ·

      In reply to Excel Formula

      you can build up a master table for “group”
      such as below:

      Row# |column A |column B
      R# 1 |womens group |stc08
      R# 2 |men group |stc09
      R# 3 |child group |stc10

      and then you can start you field and use the formula “vlookup” to match data from the above table. the defination of vlookup as follow:
      VLOOKUP=(lookup_value,table_array,col_index_num,range_lookup)

      we set a formula at cell B10:
      =vlookup(A10,A1:B3,2,false)

      and get the result as follow:
      Row# |column A(u type) |column B (formula)
      R# 10 |womens group |stc08

      OR,you set the master at [sheet 1], and you text at [sheet 2] and start at A1
      you can write the formula at [sheet 2]cell B1
      =vlookup(A1,sheet!A1:B3,2,false)
      and then, B2 will search the corelation data from [sheet 1]and match with cell A1

      and get the result as follow:
      [sheet 2]
      Row# |column A(u type) |column B (formula)
      R# 1 |womens group |stc08

      for ex 1, if you type at cell A10: men group
      Cell B10 will match with the table and show the result: stc09

      OR, if you found there were only a few group, e.g only “womens” & “men” group, and you don’t want to set a table, you can use a formula “if” to do it. the formula of cell B2 :
      =if(A1=”Women group”,”STC08″,if(A1=”Men Group”,”STC09″,if(A1<>“”,”wrong spelling”,””)))
      >pls input text in cell A1 carefully,if you input word out of “women group”/”men goup”, B2 will show the words “wrong spelling”

      • #3660066

        Excel Formula

        by cyndi ·

        In reply to Excel Formula

        The question was auto-closed by TechRepublic

    • #3660064

      Excel Formula

      by cyndi ·

      In reply to Excel Formula

      This question was auto closed due to inactivity

Viewing 2 reply threads