=INDEX(Y4:Y10,MATCH(M2,Z4:Z10,0))

[trippyd]

Hello Folks. It's been a long time since my last post as I've generally been able to find the answer. I am however now stuck and would be grateful for your help with Index/Match issue?

I have data in cells Y4:Y10 these are a money value of eg. 10, 20, 30, 40, 50, 60 & 70.
In Z4:Z10 is the Text Reference like for example = Oranges, Apples, Pears, etc.
In column "M" I have a data drop down list of my Text Reference from Z4:Z10.
In column "N" I have my value of 10, 20, 30, etc.

At point M2 and N2 the drop down list "Oranges, Apples, Pears, etc works, so I will get "(drop down in M=) Oranges (Value returned in N=) 10" - That part is correct and works. If I drag the formula down column M&N - I simply get N/A. No amount of head banging helps me get this correct.

Could / Would you help me with this please?

Thank you in advance.
Dave.

 

[alz]

How about lock your cells?

=INDEX($Y$4:$Y$10,MATCH($M2,$Z$4:$Z$10,0))​

 

[Saurabhj]

Hi Dave,

Make below change:

=INDEX($Y$4:$Y$10,MATCH(M2,$Z$4:$Z$10,0))

 

[trippyd]

How about lock your cells?

=INDEX($Y$4:$Y$10,MATCH($M2,$Z$4:$Z$10,0))​

Thank you & the following reply for your help. Even in the videos I watched I missed the use of the "$" - Works perfectly. Thank you MrExcel follows as usual. Dave

 

[trippyd]

Hi Dave,

Make below change:

=INDEX($Y$4:$Y$10,MATCH(M2,$Z$4:$Z$10,0))

Thank you & the following reply for your help. Even in the videos I watched I missed the use of the "$" - Works perfectly. Thank you MrExcel follows as usual. Dave