# Formula for spreadsheet needed, involving different combinations of 3 columns

#### cathy2007

##### New Member
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.

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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)

Hi,

If you have a combination table such as:
Book1
GHIJK
1TypeCatScorecombinationName
21gold51|gold|5Name 1
31gold201|gold|20Name 2
41gold211|gold|21Name 3
51gold221|gold|22Name 4
61gold231|gold|23Name 5
71gold241|gold|24Name 6
82gold52|gold|5Name 7
92gold202|gold|20Name 8
102gold212|gold|21Name 9
112gold222|gold|22Name 10
122gold232|gold|23Name 11
132gold242|gold|24Name 12
141silver51|silver|5Name 13
151silver201|silver|20Name 14
161silver211|silver|21Name 15
171silver221|silver|22Name 16
181silver231|silver|23Name 17
191silver241|silver|24Name 18
202Silver52|Silver|5Name 19
212Silver202|Silver|20Name 20
222Silver212|Silver|21Name 21
Sheet1

continued ...

Replies
1
Views
382
Replies
3
Views
540
Replies
4
Views
182
Replies
3
Views
203
Replies
1
Views
110

1,203,506
Messages
6,055,796
Members
444,825
Latest member
aggerdanny

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back