Index Match Function not working properly

Mychaltb

New Member
Joined
Nov 15, 2016
Messages
16
Hi all,

I have an index match formula I need some help with. The cell I enter the formula in returns the correct value so I know it is working but for some reason when I drag it down it just returns the same value as the first cell. Even when I use the Cntl + Shift + Enter it does the same thing. Is there something I am missing with using my formula? Please see formula below.


=INDEX('Sheet2'!A:B,MATCH(Y2,'Sheet2'!A:A,0),2)

Sheet2 = Lookup values where column A is the list taken from column Y on sheet 1. Column B is what I want to return.

Any advice on what I am doing wrong is greatly appreciated. Please let me know if more information is needed.

Thanks,
Mychal
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,615
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
What is in Y3? When you drag the formula down it will now match to what is in Y3, if Y2=Y3 then you will return the same item.
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
Formula looks good to me and looks like it will adjust when copied down...

=INDEX('Sheet2'!A:B,MATCH(Y3,'Sheet2'!A:A,0),2)
=INDEX('Sheet2'!A:B,MATCH(Y4,'Sheet2'!A:A,0),2)
=INDEX('Sheet2'!A:B,MATCH(Y5,'Sheet2'!A:A,0),2)

Is that what is happening when you copy down?
 

Mychaltb

New Member
Joined
Nov 15, 2016
Messages
16
Column Y is the long list of categories. In Sheet 2 Column A is the list of categories with duplicates removed) and Column B is what I want the categories to return as. See example below:

Column Y:
Company Promotions
Company Promotions
Company Event
Company Marketing
Company Promotional Sample
Company Promotional Stand


Sheet 2 Column B:
Promotions
Promotions
Events
Marketing
Promotions
Promotions

Let me know if you need more info.
 

Mychaltb

New Member
Joined
Nov 15, 2016
Messages
16

ADVERTISEMENT

FDibbons - Yes that is what is returning when I drag it down, but for some reason "promotions" is returned in all cells when I drag formula down.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Describing a problem with a supposedly non-working formula does often not help the problem owner or the problem solver.

Is it possible that you post 5 rows of the relevant data from Sheet1 and the same from Sheet2 along with the results which must obtain? Please just in words, not in some formula.
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723

ADVERTISEMENT

Agree with Aladin.

For now, lets forget about the INDEX part (and the MATCH, for that matter, too). Can you tell me what is in...
Y3
Y4
Y5
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
The cell I enter the formula in returns the correct value so I know it is working but for some reason when I drag it down it just returns the same value as the first cell.
Make sure calculation is set to automatic.
 

Mychaltb

New Member
Joined
Nov 15, 2016
Messages
16
T. Valko - Thank you! I had forgot I had turned this to manual! This did the trick, thank you so much!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,843
Members
413,944
Latest member
3xc3ln00b

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