IF function and INDEX/MATCH combined

Resolved · High Priority · Version 2010

Mon 6 Jan 2014, 10:13 Delegate Raani said...

Raani has attended:
Excel Advanced course
Excel VBA Intro Intermediate course

IF function and INDEX/MATCH combined

Hi,

Is it possible to combine an IF function with an Index Match formula?

My data set lists clients along column A and has different categories from A1 to K1 one of which is each month from 2003 to 2013 ie 'Jan-13'. So in a separate tab I want to show the monthly information for each client of certain categories. So I want the formula to say:

IF(column H in data set contains the month Jan-13, INDEX(Data set rows $A:$K, MATCH(client name, Data set A:A,0), MATCH( Category specified, data set A1:K1,0)),0)

When I take out the IF function it just returns the first line of data for that client so not specific to the month I want, however when I put in the above formula it just returns 0 values.

Thank you for your help

Raani

Wed 8 Jan 2014, 12:25 Trainer Gary said...

RE: IF function and INDEX/MATCH combined

Hi Raani

Thanks for getting in touch. Yes it is possible to embed INDEX and MATCH inside an IF function.

It's difficult to tell why yours is failing. Perhaps the best way would be to highlight the cell with the formula in, then go to the Formulas tab and press the Evaluate Formula button. Then you can run the formula step by step and see which piece of information is causing the problem.

I hope this helps.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Wed 15 Jan 2014: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Formst Excel to display leading zeroes

Select the cells that you want to have displaying leading zeroes.

From the 'Format' menu select 'Cells' (Or Right-click on the selected range and and select 'Format Cells').

In the 'Number' tab click on 'Custom' in the 'Category' window.

In the 'Type:' box enter zeroes that correspond to the size of the required number (eg 5 zeroes).

Click OK.

When you enter numbers into these cells, leading zeroes will be displayed.

eg 123 = 00123

View all Excel hints and tips