# Return number value from a range of cells

##### New Member
Hi,

Wanting to figure out how to write an excel formula to return value from an array of 5 items in which it is a "value" in column A and matches "green dot" text in column B.
In below manual example, cell E2 would return 33.7.

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Do you mean like this?

Book4
ABCDEF
1NaN
217.2red dotGreen dot33.7
3NaN
433.7green dot
5NaN
6
7
8
Sheet1
Cell Formulas
RangeFormula
E2E2=INDEX(A:A,MATCH(D2,B:B))

Close but not really!

Cell E2 needs to return 33.7 if col A4 = 33.7 and B4 = "green dot" amongst the range of data from A1:B5

Close but not really!

Cell E2 needs to return 33.7 if col A4 = 33.7 and B4 = "green dot" amongst the range of data from A1:B5
without making use of data contained in cell D2

without making use of data contained in cell D2
So then just hard-code it in the formula.
Excel Formula:
``=INDEX(A:A,MATCH("green dot",B:B))``

If that is not what you are looking, then you need to explain it in more detail.
Remember, while you are fully aware of your problem and what you are trying to do, all that we have to go on is the little bit you have provided here.

So then just hard-code it in the formula.
Excel Formula:
``=INDEX(A:A,MATCH("green dot",B:B))``

If that is not what you are looking, then you need to explain it in more detail.
Remember, while you are fully aware of your problem and what you are trying to do, all that we have to go on is the little bit you have provided here.

Hi Joe - that worked much better! Thanks

Why does this return "NaN" when I do the operation as such?

Hi Joe - that worked much better! Thanks

Why does this return "NaN" when I do the operation as such?

View attachment 54224
I was expecting the return value to be -20.9197 and NOT 'NaN'

I had copied sufiyan97's original formula, and missed the fact that he forgot the last argument of the MATCH function (which says to only accept exact matches).
Rich (BB code):
``=INDEX(A1:A3,MATCH("green dot",B1:B3,0))``

I had copied sufiyan97's original formula, and missed the fact that he forgot the last argument of the MATCH function (which says to only accept exact matches).
Rich (BB code):
``=INDEX(A1:A3,MATCH("green dot",B1:B3,0))``
The exact match using ',0' fixed it! Thank you all!

You are welcome.
Glad we were able to help.

Replies
1
Views
67
Replies
3
Views
185
Replies
1
Views
59
Replies
12
Views
165
Replies
7
Views
176

1,203,600
Messages
6,056,202
Members
444,850
Latest member
dancasta7

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