INDEX/MATCH with Multiple MATCH criteria ?

Hey guys,

I've been working on an INDEX/MATCH formula, but nothing seems to work. Tried a few combination of INDEX/MATCH with ANDs, IFs and/or VLOOKUPs, but I'm struggling with the syntax. And to make things even more complicated, the formula refers to a table from another tab in my .xlsx document.

The formula is to be entered in 'Bourse', cell G11.

What I'm trying to do:
Look through the table in 'GET - Investing', and return the appropriate SOMMAIRE (Column F) according to two match criteria...
- 'GET - Investing' SYMBOLE (Column B) must be the same as 'Bourse' cell $D11.
- 'GET - Investing' PERIODE (Column C) must be the same as 'Bourse' cell G$9.

Any input on the matter would be appreciated.
Thanks.

KJ

try this formula:

Please Login or Register  to view this content.

It works on my computer running Excel 2013 under Windows 7 Pro.
For this formula to work, you need to sort your data by SYMB then by PERIOD.

The idea behind this formula is that you need a dynamic range for your Index function.
The Offset function is used for that. It alway start at the top left corner of Get Investing table.
Then it moves down to the first occurence of the search data (Like HUX).
Then you must count how many of this data is present in the table (that's the Countif() part).
That's for the first Offset function. The second one is to find the second search parameter (hours).
The only difference with the first one is the column offset which is set to 1.

Try this new formula. It is much simpler and should work.

Please Login or Register  to view this content.

or this one which uses the regular range addressing.

Please Login or Register  to view this content.

Those are array formulas. To finish entering an array formula, you need to press, at the same time, CTRL+SHIFT+ENTER. You should see those characters ({}) at both ends of the formula.
Hope this helps