# INDEX/MATCH with Multiple MATCH criteria ?

1. ## 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

2. ## Re: INDEX/MATCH with Multiple MATCH criteria ?

try this formula:
``Please Login or Register  to view this content.``

3. ## Re: INDEX/MATCH with Multiple MATCH criteria ?

Hmmm...doesn't seem to work, at least not on MAC 2011. Returns "0" and I'm not sure what could be the problem, as I very rarely use OFFSET, not sure I understand the syntax/application of it completely.

4. ## Re: INDEX/MATCH with Multiple MATCH criteria ?

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.

5. ## Re: INDEX/MATCH with Multiple MATCH criteria ?

Tried sorting the table by SYMB and PERIOD...still returns "0". I'm tempted to blame MAC Excel though, wouldn't be the first time something works on PC, and not on MAC.

I guess I'll just go back to my older way of doing it, which was renaming the SYMB to ie. GSPTSE-1H instead of using separate columns for SYMB and PERIOD. It may not be ideal, but it's a lot simpler to write.

6. ## Re: INDEX/MATCH with Multiple MATCH criteria ?

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

7. ## Re: INDEX/MATCH with Multiple MATCH criteria ?

First one worked like a charm...and I actually understand it, so I'll have no problem tweaking it for the other cells.
Thanks you so much !!!!

There are currently 1 users browsing this thread. (0 members and 1 guests)