Trouble with Index Match multiple criteria

hi everyone,

I have the following formula:

=INDEX('Feb 2014'!E3:E500,MATCH('Answer Analysis'!B3,'Feb 2014'!B3:B500)*('Answer Analysis'!D3,'Feb 2014'!D3:D500),0)

So i'm trying to return the results from Feb 2014 column E if the value in B2 is matched with the Feb 2014 B column value and D2 is matched wih the Feb 2014 D column value. I'm entering Ctrl+Shift+Enter as well. I'm getting a #value error. What am I doing wrong?

The end results is that I'll need to add the formula about 4 times (for 4 different months)

Thanks!

Hi,

Not entirely sure what you expect (D2,'Feb 2014'!D2:D500) to evaluate to but at the moment it will be an error.
Neither am I sure what the Match(B2.... is doing since if B2 is also on the Feb 2014 sheet that bit will always result in the value 1, since B2 will always be found in the first row of B2:B500.

Upload your workbook and manually add the results you expect to see explaining clearly how you have calculated them

Sorry, but out of context that doesn't mean a lot.

Please upload the workbook with manually calculated results as requested.

=INDEX('Feb 2014'!E3:E500,MATCH(1,('Answer Analysis'!B3='Feb 2014'!B3:B500)*('Answer Analysis'!D3='Feb 2014'!D3:D500),0))

Could it be this that you were trying to achieve? Note the added 1,( and the parethesis in the end.
An array formula ofcourse, you have to enter it with Ctrl + Shift + Enter.

Also the fixed ranges like E3:E500 should be $E$3:$E$500.

One way...

Array entered**:

=INDEX('Feb 2014'!E3:E500,MATCH('Answer Analysis'!B3,IF('Feb 2014'!D3:D500='Answer Analysis'!D3,'Feb 2014'!B3:B500),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Right now I can think of three different ways to do the multiple condition lookup:
MATCH(A1&B1, E1:E100&F1:F100,0)
MATCH(B1,IF(E1:E100=A1,F1:F100),0)
MATCH(1,(E1:E100=A1)*(F1:F100=B1),0)

I haven't tested them all but I'm guessing the top one is the slowest and the bottom one is the fastest. Do you have any input on that, Tony?

Here are the average calculation times (in seconds) for these formulas:

Formula1: MATCH(A1&B1, E1:E100&F1:F100,0)
Formula2: MATCH(B1,IF(E1:E100=A1,F1:F100),0)
Formula3: MATCH(1,(E1:E100=A1)*(F1:F100=B1),0)

Data Range

	I	J	K	L	M	N	O
1	100 rows	Test1	Test2	Test3	Test4	Test5	Average
2	Formula1	0.00029	0.00029	0.00029	0.00032	0.00029	0.000296
3	Formula2	0.00017	0.00017	0.00017	0.00017	0.00017	0.000170
4	Formula3	0.00017	0.00017	0.00017	0.00017	0.00017	0.000170
5
6
7	1000 rows	Test1	Test2	Test3	Test4	Test5	Average
8	Formula1	0.00864	0.00863	0.00761	0.00861	0.00866	0.00843
9	Formula2	0.00044	0.00044	0.00046	0.00045	0.00044	0.000446
10	Formula3	0.00052	0.00052	0.00054	0.00052	0.00053	0.000526

Used the timer code found here:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

Your results may vary depending on the configuration of your machine.

Notice how inefficient the range concatenation formula is on the larger range. Yet, you see many folks doing it that way!

Good info! Thanks Tony!