Index Match with 2 or more criteria

[philip.michael.smith]

Hello,

I know this question has been asked a lot but all of the answers I am finding contains a formula that looks something like this --> =INDEX($D$2:$D$9,MATCH($B$11&B$14,$B$2:$B$9&$G$2:$G$9,0))

However, what I am trying to accomplish is to pull data from one spreadsheet that meets criteria in two different columns and then coping the formula on my other spreadsheet in multiple areas (highlighted in yellow). I know this isn't the best explanation so I attached a spreadsheet.

Thanks for any advice.

[jeversf]

Hi Philip

i had to tweak one of the columns but this should help. You needed to add another match function for looking up the column.

[FlameRetired]

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.

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.

[philip.michael.smith]

Thank you both for your solutions! What I am gathering from both of your answers is that you have to have flat data in order to get the formula's to work. Is there another formula that can generate the answer without flat data and without changing the format?

The reason I ask is because the spreadsheet that I am trying to get the data from is huge and by copying Practice 1, Practice 2, etc next to each BCBC, Commercial, etc will take awhile.

This gives me a lot to think about.
Thanks

[FlameRetired]

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.

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

[philip.michael.smith]

That's a neat trick. So I can understand what is happening, are the "zzzz" like a placeholder in Excel?

[FlameRetired]

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.

[philip.michael.smith]

I am going to try this along with your formula tomorrow and see if this will solve my problem.
Thanks,

[FlameRetired]

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

[Pete_UK]

I've also changed column I in your file (coloured blue), and then put this array* formula in C3:

=IFERROR(INDEX($K$2:$O$27,MATCH(LOOKUP("zzz",$A$1:$A3)&$B3,$I$1:$I$18&$J$2:$J$18,0),COLUMNS($C:C)),"")

This can be copied across to G3, and then down to your other yellow cells, as can be seen in the attached file.

*Note: an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual < Enter >.

Hope this helps.

Pete

[philip.michael.smith]

Thanks everyone for your input. I was able to improve the spreadsheet I was working on exponentially!

[FlameRetired]

Glad to hear it. Thanks for letting us know.