Designation of various cells

[wizardmagu]

Based on data in many cells I will be designating the columns into the following categories:
*Type 1
*Type 2
*Type 3
*Type 4
A single row in excel will have multiple columns of various designations of the above types and I want to pick designate the entire row to the lowest Type from list above.

For example if Row 1 has:
A B C D
Row 1 Type 1 Type 1 Type 3 Type 2 Then the final output of this row of data will be Type 3 (in column/row E1) because that is the lowest type available from the 4 types available.

I am trying to come up with a way to give a "Final" designation. Hope this explanation makes sense.

 

[StephenCrump]

Do you mean something like this?

	A	B	C	D	E
1	Scores
2	Nil
3	Low
4	Medium
5	High
6
7	Criterion
8	1	2	3	4	Overall
9	Nil	Nil	Medium	Nil	Medium
10	Low	Low	Medium	High	High
11	Low	Low	Low	Nil	Low
12	Medium	Nil	Low	Nil	Medium
Sheet1

Cell Formulas
Range	Formula
E9:E12	E9	=INDEX(Criteria,MAX(MATCH(A9:D9,Criteria,)))

Named Ranges
Name	Refers To	Cells
Criteria	=Sheet6!$A$2:$A$5	E9:E12

You may need to array-enter this formula (i.e. CTRL-SHIFT-ENTER) depending on your version of Excel.

I suggest you update your Account details so we know what version of Excel you're using - the best solution often depending on Excel version.

 

[wizardmagu]

Yes, this is exactly what I was looking for. I would have not figured this out on my own...

I updated my details for version of Excel.

Thanks again

 

[StephenCrump]

That's great, I'm glad we could help.

And thanks for updating your account details.

 

[wizardmagu]

So I ran into a problem if one of the cells in the data section blank because of the if(error(vlookup formula i have there.... Is there a way to add to the formula so it would omit blanks and still use the Scores criteria on the remaining?

 

[StephenCrump]

Try:

	A	B	C	D	E
1	Scores
2	Nil
3	Low
4	Medium
5	High
6
7	Criterion
8	1	2	3	4	Overall
9	Nil	Nil	Medium		Medium
10	Low	Low	Medium	High	High
11					n/a
12	Medium				Medium
Sheet1

Cell Formulas
Range	Formula
E9:E12	E9	=IF(COUNTA(A9:D9),INDEX(Criteria,MAX(IFERROR(MATCH(A9:D9,Criteria,),0))),"n/a")

Named Ranges
Name	Refers To	Cells
Criteria	=Sheet5!$A$2:$A$5	E9:E12

 

[wizardmagu]

That is AWESOME!!!! Thanks for the help with this.