Join or sign in Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below. Use Your Email Use FacebookUse Linkedin

Excel Formula

Locked

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.

Topic

Excel Formula

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

Excel Formula

In reply to Excel Formula

The question was auto-closed by TechRepublic

Excel Formula

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”

Excel Formula

In reply to Excel Formula

The question was auto-closed by TechRepublic

Excel Formula

In reply to Excel Formula

This question was auto closed due to inactivity

[Author]

Replies