Array Index/Match + ISNUMBER/Search not returning result?

[igmike]

1) I am having trouble entering the formula as an array. I press control + Shift + enter, and nothing happens (using office professional plus 2013)

2) Not sure if the formula is correct, I'd like to pull the number into the adjacent (C) column.

Excel Workbook
	B	C
2	Title	VGA Grade
3	NEW PlayStation 3 PS3 CALL of DUTY Black Ops Bundle 160GB VGA 85+ NM+Item ID: 272826824787Seller: 5***v	{=INDEX({"100","95+","95","90+",85+","85"},MATCH(TRUE,ISNUMBER(SEARCH({"100","95+","95","90+",85+","85"},B3)),0))}
4	NEW PlayStation 3 PS3 CALL of DUTY Black Ops Bundle 160GB VGA 85+ NM+Item ID: 272839937690Seller: 5***v
5	NEW PlayStation 3 PS3 CALL of DUTY Black Ops Bundle 160GB VGA 85+ NM+Item ID: 272888440351Seller: 5***v
6	Red Dead Redemption SPECIAL EDITION, New Sealed! Sony PS3 VGA 90+Item ID: 201778548898Seller: e***e
price-guide

 

[shaowu459]

please try

=INDEX({"100","95+","95","90+","85+","85"},MATCH(TRUE,ISNUMBER(SEARCH({"100","95+","95","90+","85+","85"},B3)),0))

you missed double quotation marks for 85+

 

[Aladin Akyurek]

@ igmike

In C3 just enter and copy down:

=LOOKUP(9.99999999999999E+307,FIND(" "&{85;90;95;100;"85+";"90+";"95+"}," "&$B3),{85;90;95;100;"85+";"90+";"95+"})

Note that I added 90 to the grade list. If this omission is intended, remove this addition from the constant array.

 

[igmike]

@ Aladin

Thank you. That works perfect for what I need, I will see if I can solve step 2, if not post a new thread with info.

 

[Aladin Akyurek]

@ Aladin

Thank you. That works perfect for what I need, I will see if I can solve step 2, if not post a new thread with info.

You are welcome. Thanks for the update.