Desktop

General discussion

Locked

Excel Formula

By ·
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.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

Collapse -

Excel Formula

by 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

Collapse -

Excel Formula

by In reply to Excel Formula

The question was auto-closed by TechRepublic

Collapse -

Excel Formula

by 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"

Collapse -

Excel Formula

by In reply to Excel Formula

The question was auto-closed by TechRepublic

Collapse -

Excel Formula

by In reply to Excel Formula

This question was auto closed due to inactivity

Related Discussions

• 3

How is Dell XPS 2020 Desktop Quality? Is it worth the buy?

Simpleit2020 ·

• 4

Having trouble with motherboard/CPU

sawyermchaney ·

• 1

CraigC_ ·

• 1

Quick steps.

sylvandkev21 ·

• 4

My father has a late 2013 IMac 27". Is it possible to upgrade cpu?

zackcole700 ·