Formula for spreadsheet needed, involving different combinations of 3 columns

[cathy2007]

Hi,

I'm working with 3 columns in an excel spreadsheet; type, category, and score, and based on combinations of the 3, an overall field name is designated. In type, there are 2; type 1, and type 2. In category, there are 3; gold, silver and copper. In score, there are 5; 20, 21, 22, 23, and 24. In overall, there are various overall field names depending on the combination of type, category and score.

So for example, if column A is type 1, column B is silver, and column C is a score of 21, then the overall field name is 'middle'. I have the 3 pieces of information already populated in my spreadsheet; type, category, and score, but I need a formula in column D to look at these 3 fields, and decide what the overall field name should be. So - automatically populate the 'overall', based on the other 3.

I've seen this done before using a separate tab to map out the data...but I don't have the formula for reference, and I've tried many formulas without luck. Please let me know if you're able to help. Thank you so much to all that provide suggestions!

I've tried IF statements, but there are too many combinations.

 

[xld]

Create a table with all combinations in the key column (first) and the lookup values in the second, something like

Type1Silver21.....middle

etc

and then use

=VLOOKUP(A2&B2&C2,table,2,False)

 

[al_b_cnu]

Hi,

If you have a combination table such as:

Book1
	G	H	I	J	K
1	Type	Cat	Score	combination	Name
2	1	gold	5	1|gold|5	Name 1
3	1	gold	20	1|gold|20	Name 2
4	1	gold	21	1|gold|21	Name 3
5	1	gold	22	1|gold|22	Name 4
6	1	gold	23	1|gold|23	Name 5
7	1	gold	24	1|gold|24	Name 6
8	2	gold	5	2|gold|5	Name 7
9	2	gold	20	2|gold|20	Name 8
10	2	gold	21	2|gold|21	Name 9
11	2	gold	22	2|gold|22	Name 10
12	2	gold	23	2|gold|23	Name 11
13	2	gold	24	2|gold|24	Name 12
14	1	silver	5	1|silver|5	Name 13
15	1	silver	20	1|silver|20	Name 14
16	1	silver	21	1|silver|21	Name 15
17	1	silver	22	1|silver|22	Name 16
18	1	silver	23	1|silver|23	Name 17
19	1	silver	24	1|silver|24	Name 18
20	2	Silver	5	2|Silver|5	Name 19
21	2	Silver	20	2|Silver|20	Name 20
22	2	Silver	21	2|Silver|21	Name 21
Sheet1

continued ...

 

[al_b_cnu]

... continued

You can use VLookup as follows:

Book1
	A	B	C	D
1	Type	Cat	Score	Name
2	1	gold	5	Name 1
3	2	silver	20	Name 20
4	1	Copper	21	Name 27
5	2	gold	22	Name 10
6	1	silver	23	Name 17
7	2	Copper	24	Name 36
8	1	gold	5	Name 1
9	2	silver	20	Name 20
10	1	Copper	21	Name 27
11	2	gold	22	Name 10
12	1	silver	23	Name 17
13	2	Copper	24	Name 36
Sheet1

Formula in D2 copied down is =VLOOKUP(A2&"|"&B2&"|"&C2,J:K,2,FALSE)