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

trippyd

New Member
Joined
Jun 21, 2017
Messages
14
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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

alz

Board Regular
Joined
Jul 17, 2020
Messages
130
Office Version
  1. 2019
Platform
  1. Windows
How about lock your cells?

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

 

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
451
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi Dave,

Make below change:

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

trippyd

New Member
Joined
Jun 21, 2017
Messages
14
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
 
  • Like
Reactions: alz

trippyd

New Member
Joined
Jun 21, 2017
Messages
14
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,640
Messages
5,654,535
Members
418,138
Latest member
agnesegras

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top