Index Match with 2 or more criteria

I, too, took some liberties with the layout. Offset row headers pose the same problems as merged cells. They cause havoc with formulas.

To expedite matters the source needs to be laid out as a flat data base. Have a contiguous column of Practice 'codes'. This allows for unique pairings.

I did the same in the output section.

The formula I used in C3 is array-entered, then filled down and across to G27.

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. Curly braces {} around the formula in the formula bar confirm correct entry.

Rather than copy / paste you might try inserting a temporary helper column adjacent to the original.


Then use something like this to fill in the blanks.

Formula:

Please Login or Register  to view this content.

When you are finished copy whole range of results and paste Values back onto itself. Then delete the original column.

Before you delete the original column it would resemble this:

Row\Col	A	B
1	Practice 1	Practice 1
2		Practice 1
3		Practice 1
4		Practice 1
5		Practice 1
6	Practice 2	Practice 2
7		Practice 2
8	Practice 3	Practice 3
9		Practice 3
10		Practice 3
11		Practice 3
12		Practice 3
13		Practice 3
14		Practice 3
15	Practice 4	Practice 4
16		Practice 4
17		Practice 4
18		Practice 4
19		Practice 4
20		Practice 4
21		Practice 4

No it 'instructs' LOOKUP to find the last string less than or equal to a sufficiently larger than likely string ... zzzzz .... in this case. This assures that LOOKUP will always return the last item in the reference range.

It can be used for numbers as well with a sufficiently large number in the first argument.

I often use is for helper columns when merged cells are present.

You're welcome. Thanks for the feedback. Let us know how it turns out.

Glad to hear it. Thanks for letting us know.