Look up formula query?

[kbry84]

Hi all,
Im really not all that excel-savvy and ive been trying so many different options for my problem...

Basically i have a column which has 10 cells with drop down options i can select from of:

• N/A
• Not Achieved
• Progressing
• Addressed
• Strength

I basically want a formula that can tell me if at least one of the cells has Not Achieved selected, it should return this as the overall result. If there is no Not Achieved in the cells but there is more than one Progressing, it should return the result Progressing and so on and so forth so basically the overall result always chooses the least desirable selection starting from Not Achieved and going right up to Strength. So for the outcome to be Strength, all 10 cells would need to have Strength selected.

I hope that makes sense!
Any direction would be immensely appreciated!!

 

[Adjunctor]

try this: (assuming your data is on A2:A11, if not just replace this with your own range.

{=CHOOSE(MIN(IF($A$2:$A$11="Achieved",1,IF($A$2:$A$11="Progressing",2,IF($A$2:$A$11="Addressed",3,IF($A$2:$A$11="Strenght",4,5))))),"Achieved","Progressing","Addressed","Strength","")}

Notice the brackets {} wrapping the formula? this means it's an array formula; you don't type the brackets, instead hold down keys Ctrl + Shift and then press Enter.

If all cells are "N/A" the formula will return "", corresponding to option 5, of course you can change this to "N/A" or other non empty string.

 

[Drrellik]

KBRY,

I think I understand what you want. I had to create a couple of helper columns to use vlookup and had to arrange them A-Z so it would work properly. this may be somewhat convoluted, but I hope it helps you.
If you have any cells with "Not Achieved" the result is the same. if you have 9 with Strength and 1 with Addressed the result is Addressed. it take the lowest from the list.

Excel 2012
	A	B	C	D	E	F
1	Addressed	3	Addressed	3	1	Not Achieved
2	Strength	4	N/A		2	Progressing
3	Addressed	3	Not Achieved	1	3	Addressed
4	N/A		Progressing	2	4	Strength
5	Addressed	3	Strength	4		N/A
6	Addressed	3
7	Strength	4
8	Addressed	3
9	Strength	4
10	Addressed	3
11
12	Addressed	3
Sheet1

Cell Formulas
Range	Formula
B1	=IF(INDEX($C$1:$D$5,MATCH(A1,$C$1:$C$5),2)=0,"",(INDEX($C$1:$D$5,MATCH(A1,$C$1:$C$5),2)))
B2	=IF(INDEX($C$1:$D$5,MATCH(A2,$C$1:$C$5),2)=0,"",(INDEX($C$1:$D$5,MATCH(A2,$C$1:$C$5),2)))
B3	=IF(INDEX($C$1:$D$5,MATCH(A3,$C$1:$C$5),2)=0,"",(INDEX($C$1:$D$5,MATCH(A3,$C$1:$C$5),2)))
B4	=IF(INDEX($C$1:$D$5,MATCH(A4,$C$1:$C$5),2)=0,"",(INDEX($C$1:$D$5,MATCH(A4,$C$1:$C$5),2)))
B5	=IF(INDEX($C$1:$D$5,MATCH(A5,$C$1:$C$5),2)=0,"",(INDEX($C$1:$D$5,MATCH(A5,$C$1:$C$5),2)))
B6	=IF(INDEX($C$1:$D$5,MATCH(A6,$C$1:$C$5),2)=0,"",(INDEX($C$1:$D$5,MATCH(A6,$C$1:$C$5),2)))
B7	=IF(INDEX($C$1:$D$5,MATCH(A7,$C$1:$C$5),2)=0,"",(INDEX($C$1:$D$5,MATCH(A7,$C$1:$C$5),2)))
B8	=IF(INDEX($C$1:$D$5,MATCH(A8,$C$1:$C$5),2)=0,"",(INDEX($C$1:$D$5,MATCH(A8,$C$1:$C$5),2)))
B9	=IF(INDEX($C$1:$D$5,MATCH(A9,$C$1:$C$5),2)=0,"",(INDEX($C$1:$D$5,MATCH(A9,$C$1:$C$5),2)))
B10	=IF(INDEX($C$1:$D$5,MATCH(A10,$C$1:$C$5),2)=0,"",(INDEX($C$1:$D$5,MATCH(A10,$C$1:$C$5),2)))
B12	=MIN(B1:B10)
A12	=VLOOKUP(B12,E1:F5,2)

~DR

 

[Drrellik]

Looks like Adjunctor got it done and without the helper cells, I started working on a nested IF similar but got side tracked and went a different route. Thanks for that Adjunctor.

~DR

 

[kbry84]

Thanks so so much for getting back to me!

Ive pasted that in but with the range of text i have in cells A2-A11 it doesnt seem to be displaying the right answer. So in A2-11 i have as an example:

Strength
Progressing
Not Achieved
N/A
Strength
Addressed
Progressing
Not Achieved
N/A
Strength

And its displaying Progressing but should show Not Achieved.

N/A i dont want included so Not Achieved is the lowest outcome therefore even with this appearing once in cells A2-A11 i want the result to display Not Achieved. Then if Not Achieved isnt in the list but Progressing is this would be the next one that should appear, followed by Addressed and Strength. So like Drrellik said, even if there is 9 Strength and 1 Addressed the result would be Addressed.

Thanks so much for all your help!

 

[Adjunctor]

You are very welcome!

 

[RonB1111]

It looks like Adjunctor's formula had a couple typos resulting in not getting the result you're looking for. Try this (corrections noted in red) and remember to confirm with Ctrl+Shift+Enter:

=CHOOSE(MIN(IF($A$2:$A$11="[B][COLOR=#ff0000]Not [/COLOR][/B]Achieved",1,IF($A$2:$A$11="Progressing",2,IF($A$2:$A$11="Addressed",3,IF($A$2:$A$11="Streng[B][COLOR=#ff0000]th[/COLOR][/B]",4,5))))),"[B][COLOR=#ff0000]Not[/COLOR][/B] Achieved","Progressing","Addressed","Strength","")

 

[Adjunctor]

Sorry for that, I didn't test the formula so I couldn't catch my misspelling. Thanks Ron for getting it straight.

 

[kbry84]

Oh wow i think thats got it!! thank you so much, you're all amazing!