Index and Match array formula - Help with this?

Your formula is missing the sheet reference to your second range in the match, and you have to enter it as an array formula with Ctrl+Shift+Enter, instead of just hitting enter as normally.

Also, if you're going to use the 4th column of that index, you can just specify D for the whole thing.

=INDEX(HR!$D$2:$D$27079,MATCH(Practice!B2&C2,HR!$B$2:$B$27079&HR!$C$2:$C$2709,0))

You can also use LOOKUP to avoid using an array formula altogether:

=LOOKUP(2,1/((Practice!B2=HR!$B$2:$B$27079)*(C2=HR!$C$2:$C$2709)),HR!$D$2:$D$27079)

Hi,

A better way would be to use helper columns which concatenate columns B&C. Otherwise as an array formula. i.e. one you enter with Ctrl-Shift-Enter. For instance in Practice!D1 copied down.

Formula:

=INDEX(HR!$D$1:$D$27079,MATCH(B1&C1,HR!B:B&HR!C:C,FALSE),1)

..and don't forget the Ctrl-Shift-Enter

A couple of issues.

First, range concatenation is inefficient, especially on large ranges.

Not all of your ranges are the same size. The reference to column C goes down to row 2709 while all other references go down to row 27079.

Is the lookup value supposed to be:

Practice!B2 & Practce!C2

Here's an example of how to do a multiple criteria lookup.

Data Range

	A	B	C	D	E	F	G
1	Header1	Header2	Header3	------	Lookup1	Lookup2	Result
2	1	1	Result1		2	3	Result5
3	1	2	Result2
4	2	1	Result3
5	2	2	Result4
6	2	3	Result5
7	3	1	Result6
8	3	2	Result7
9	4	1	Result8
10	4	2	Result9

This array formula** entered in G2:

=INDEX(C2:C10,MATCH(F2,IF(A2:A10=E2,B2:B10),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.

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.