Index & Match a table of data using multiple parameters

[monaghanmj44]

Hi,

First time on this forum. I desperately need help with the following issue.
Cannot figure out how to match parameters based on more than one cell on a data sheet and lookup the instersecting result in a table another sheet (Sheet2).

There are 3 lookup parameters in this particular case. 2 cells cover a business name and business unit which need to match with first 2 rows of cells in the Sheet2!table followed by the corresponding business 'status' which can alter from month to month. There is 60 months of current and historical Business/Unit status in the table.

The third parameter cell is a month/year date (e.g. Sep13) which can be manually altered and matches with the column headings in the Sheet2 table.

I have provided an example.xlxs attachment which I hope makes more sense.
I have a had a few goes with Index and Match but just cannot get a handle on this.

Any help would be very grateful

Thanks
MJM

[FDibbins]

Hi and welcome to the forum

Try this ARRAY formula in B19...
=INDEX($A$2:$F$13,MATCH($B$17&$B$18,$A$2:$A$13&$B$2:$B$13,0),MATCH($B$16,$A$2:$F$2))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[sktneer]

Another way to do this.....

=INDEX($C$3:$F$13,MATCH($B$18,IF($A$3:$A$13=$B$17,$B$3:$B$13,""),0),MATCH($B$16,$C$2:$F$2,0))

This is an array formula so it needs to be confirmed with Ctrl+Shift+Enter

[azumi]

Non Array :

=INDEX(B3:F13,0,MATCH(B16,B2:F2)) INDEX(B3:F13,MATCH(B18,B3:B13),0)

or

=VLOOKUP(B17,A3:F13,MATCH(B16,B2:F2,0),TRUE)

Azumi