Index and Match with 2 criterias. Arrayproblem.

[norsklaks]

Hi!

I used this from a tutorial so I might not understand everything from it.

I use this formula:

=INDEX('Sheet2'!$D$2:$D$39087;MATCH(H2&K2;'Sheet2'!$N$2:$N$39087&'Sheet2'!$B$2:$B$39087;0))

The Match formula finds a value from H2 (from Sheet1) and matches it with a value in the N row AND also matches the value in K2 (from Sheet1) with a value in the B row. The index then returns the value based on the row that matches these two criterias.

My problem is that I have to press shift+control+enter (or F9) to make this formula work, otherwise it just says "#VALUE!". The problem is that this only works with 1 row at a time, and as I have hundreds of them it is very time consuming.

Anyone know how to make this work with several rows at a time?

[skywriter]

Could you please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.

ViewPic

[Tony Valko]

Range concatenation is inefficient (slow to calculate) especially on large ranges.

Try it like this...

Array entered**:

=INDEX(Sheet2!$D$2:$D$39087;MATCH(1;(Sheet2!$N$2:$N$39087=H2)*(Sheet2!$B$2:$B$39087=K2);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.

Anyone know how to make this work with several rows at a time?

Array enter the formula in the first cell then just copy down as needed.

[Jacc]

Sorry for hijacking, I just have to ask something.
Tony, didn't you used to recommend the IF solution to the match problems like this rather than the multiplication tacticts used here? Which is really better?

[Tony Valko]

I used to think nested IFs were faster to calculate, having seen claims by other high profile Excel experts, but in recent tests I have found that on my machine array multiplication is faster to calculate.

[norsklaks]

I did what you said and it worked perfectly fine, Tony! I arrayentered the formula in the first cell then just "dragged" it down onto the others. I think I tried it before but it only copied the first row. Not sure why it didnt work last time.

Thank you so much!

[Tony Valko]

Just did a quick test.

Tested these 2 array formulas in Excel 2002:

=INDEX(Range3,MATCH(1,IF(Range1>=75,IF(Range2>=75,1)),0))

=INDEX(Range3,MATCH(1,(Range1>=75)*(Range2>=75),0))

Tested each formula on 100 rows of data then on 1000 rows of data then on 10000 rows of data. Got the average time for 5 calculations. Here are the results:

Data Range

	E	F	G	H	I	J	K
2	100 Rows	Test1	Test2	Test3	Test4	Test5	Average
3	Array Mult	0.00021	0.00023	0.00024	0.00024	0.00024	0.00023
4	Nested IF	0.00025	0.00026	0.00026	0.00026	0.00027	0.00026
5
6	1000 Rows
7	Array Mult	0.00094	0.00095	0.00094	0.00094	0.00095	0.00094
8	Nested IF	0.00104	0.00104	0.00103	0.00104	0.00105	0.00104
9
10	10000 Rows
11	Array Mult	0.00773	0.00773	0.00774	0.00786	0.00773	0.00776
12	Nested IF	0.00857	0.00856	0.00856	0.00857	0.00857	0.00857

Used the timer code found here:

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

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[norsklaks]

Done! Thanks!