Index Match Function not working properly

[Mychaltb]

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

 

[Scott T]

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]

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]

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]

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]

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]

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]

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]

T. Valko - Thank you! I had forgot I had turned this to manual! This did the trick, thank you so much!

 

[T. Valko]

You're welcome! Thanks for the feedback!