# Look up formula query?

#### kbry84

##### New Member
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
• 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!!

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
try this: (assuming your data is on A2:A11, if not just replace this with your own range.

Code:
``{=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.

Last edited:
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
ABCDEF
2Strength4N/A2Progressing
4N/A Progressing24Strength
7Strength4
9Strength4
11
Sheet1
Cell Formulas
RangeFormula
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

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

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
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!

You are very welcome!

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:
Code:
``=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","")``

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

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

Replies
3
Views
341
Replies
10
Views
457
Replies
5
Views
709
Replies
9
Views
649
Replies
1
Views
2K

1,203,534
Messages
6,055,956
Members
444,839
Latest member
laurajames

### 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