IF function

[laidback]

I have 15 text items that can be sorted into 6 groups. I want to be able to take a cells text information and assign a value based on which group it is in.

Here is an example.

Group1
->item a
->item b
Group 2
->item c
etc


what I want to have happen is if a3 contains item c I want group two's value.

I hope this makes sense.
Thanks in advance

[mechpm]

[Aladin Akyurek]

On 2002-05-22 15:06, laidback wrote:
I have 15 text items that can be sorted into 6 groups. I want to be able to take a cells text information and assign a value based on which group it is in.

Here is an example.

Group1
->item a
->item b
Group 2
->item c
etc


what I want to have happen is if a3 contains item c I want group two's value.

I hope this makes sense.
Thanks in advance

Make a 2-column list of items (I assume they are unique) in some range and name it e.g, List via the Name Box on the Formula Bar.

List would look like this:

a 1
b 1
c 2
d 3
e 3
f 4
g 4
h 4

etc. where the second column list the values for the groups the items belong to.

Once you have this setup, you ca use:

=VLOOKUP(A1,List,2,0)

where A1 houses an item to which you want to assign a group value.

[Yogi Anand]

If your first data set is in a tabla A1:B3, and your Group-associated values are in another table D1:E2, then you can use the following formula, say in cell G1 to read the desired value

=INDEX(D1:E2,MATCH("Group2",D1:D2,0),2) ... to get ValueGroup2

as presented in the following worksheet simulation:

Microsoft Excel - Book2
D9		=
	A	B	C	D	E	F	G	H
1	a	Group1		Group1	ValueGroup1		:alert('=INDEX(D1:E2,MATCH("Group2",D1:D2,0),1)')>Group2
2	b	Group1		Group2	ValueGroup2
3	c	Group2
4
5
Sheet1

Click on a hyperlinked cell to see the underlying formula

_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature

[ This Message was edited by: Yogi Anand on 2003-01-19 13:00 ]