Return number value from a range of cells

[bassaad17]

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.

 

[Sufiyan97]

Hello bassaad17

Do you mean like this?

Book4
	A	B	C	D	E	F
1	NaN
2	17.2	red dot		Green dot	33.7
3	NaN
4	33.7	green dot
5	NaN
6
7
8
Sheet1

Cell Formulas
Range	Formula
E2	E2	=INDEX(A:A,MATCH(D2,B:B))

 

[bassaad17]

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

 

[bassaad17]

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

 

[Joe4]

without making use of data contained in cell D2

So then just hard-code it in the 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.

 

[bassaad17]

So then just hard-code it in the 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?

 

[bassaad17]

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'

 

[Joe4]

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).

=INDEX(A1:A3,MATCH("green dot",B1:B3,0))

 

[bassaad17]

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).

=INDEX(A1:A3,MATCH("green dot",B1:B3,0))

The exact match using ',0' fixed it! Thank you all!

 

[Joe4]

You are welcome.
Glad we were able to help.