Retriveing multiple matches based on multiple criteria

[oyvimoen]

Hey MrExcel.

My first post on this forum, although I've been using it quite frequently these last weeks as I'm working on a rather comprehensive workbook.

So, my problem is this;
I have several ranges from about 200 to 500 rows, each with about 10 columns. I need to retrieve spesific values from these ranges, based on several criteria. An example from one of my ranges is shown below:

Code	Left 4 code	Left 5 code	Left 6 code	Letter code	Unit	Unit price		Match code	Match letter	Match unit
41.1	41.1	41.1	41.1	I	m	317,67		43.1	B	m
42.1	42.1	42.1	42.1	I	m	530,17			C
42.2	42.2	42.2	42.2	C	m	662,67			G
42.3	42.3	42.3	42.3	B	m	553,33			H
42.3	42.3	42.3	42.3	C	m	620,33
42.4	42.4	42.4	42.4	B	m	449,67
43.11	43.1	43.11	43.11	B	m	75,33
43.11	43.1	43.11	43.11	H	m	102,17
43.11	43.1	43.11	43.11	G	m	102,17
43.11	43.1	43.11	43.11	F	m	102,17
43.11	43.1	43.11	43.11	C	m	76,17
43.12	43.1	43.12	43.12	B	m	106,33
43.12	43.1	43.12	43.12	C	m	107,07
43.14	43.1	43.14	43.14	C	m	136,97
43.37	43.3	43.37	43.37	I	m	862,50

<tbody>
</tbody>

Lets say "Code" is in cell A1.

Now, I am looking for the unit prices in column G. However, I only want to include them if my match code (cell I2) is equal to any of the values in column A or column B. (Column B because the number of characters in "match code" is 4. If it were 5 or 6, it would have to match with either column A or C/D). In addition, the code letter in column E has to match with one of the match letter (Cells J2:J5), and match unit has to match with the unit letter in column F.

If this is working correctly, the bold numbers in column G should be retrieved in a new list (or if possible, calculate the mean value right away).

I've been trying out this array formula:
=IFERROR(INDEX($A$1:$H$15;SMALL(IF($B$2:$B$16=$I$2;ROW($B$2:$B$16));ROW(A1));7);" ")

Which works great for the one criteria. However, if I try to include several matching criteria in the "if"-sentence (and/or), it returns too many values.

If anyone here have some insight or tips on what I should be doing differently, that would be greatly appreciated.

Best
oyvimoen

 

[ITWare2008]

Hi Oyvimoen,

Just type this formula in a blank cell

=MEDIAN((A2:A16=E1)*((B2:B16=F1)+(B2:B16=F2)+(B2:B16=F3)+(B2:B16=F4))*(C2:C16))

Hope this help.

Vândalo

 

[StefanVL]

Not sure if i understand correctly but this is my attempt;

=IFERROR(INDEX($G$2:$G$16;SMALL(IF($B$2:$B$16=$I$2;IF($F$2:$F$16=$K$2;IF(ISNUMBER(MATCH($E$2:$E$16;$J$2:$J$5;0));ROW($F$2:$F$16))));ROWS($L$2:L2))-1);"")

 

[oyvimoen]

Thanks Stefan, this is definitely a step in the right direction. I think I can work with this. How ever, a more sophisticated solution would be something like;

=IFERROR(INDEX($G$2:$G$16;SMALL(IF(OR($A$2:$A$16=$I$2;$B$2:$B$16=$I$2);IF($F$2:$F$16=$K$2;IF(ISNUMBER(MATCH($E$2:$E$16;$J$2:$J$5;0));ROW($F$ 2:$F$16))));ROWS($L$2:L2))-1);"")

This is supposed to check if cell I2 matches with the cell value in col A OR col B. Now, I can't get this to work properly. I don't even know if it's doable, but if it is, some assistance would be greatly appreciated.

Oyvimoen

 

[StefanVL]

Thanks Stefan, this is definitely a step in the right direction. I think I can work with this. How ever, a more sophisticated solution would be something like;

=IFERROR(INDEX($G$2:$G$16;SMALL(IF(OR($A$2:$A$16=$I$2;$B$2:$B$16=$I$2);IF($F$2:$F$16=$K$2;IF(ISNUMBER(MATCH($E$2:$E$16;$J$2:$J$5;0));ROW($F$ 2:$F$16))));ROWS($L$2:L2))-1);"")

This is supposed to check if cell I2 matches with the cell value in col A OR col B. Now, I can't get this to work properly. I don't even know if it's doable, but if it is, some assistance would be greatly appreciated.

Oyvimoen

This should be enough to fix the problem:

=IFERROR(INDEX($G$2:$G$16;SMALL(IF($A$2:$B$16=$I$2;IF($F$2:$F$16=$K$2;IF(ISNUMBER(MATCH($E$2:$E$16;$J$2:$J$5;0));ROW($F$2:$F$16))));ROWS($L$2:L2))-1);"")

Only difference is the bold part

i did not have the chance to check what happens if 43.1 appears on both columns in a single row. But it should work for a single appearance.

 

[StefanVL]

Found out that the previous solutions showed a price double if 43.1 would appear in both columns, this should prevent it:

=IFERROR(INDEX($G$2:$G$16;SMALL(IF(--($A$2:$A$16=$I$2)+--($B$2:$B$16=$I$2);IF($F$2:$F$16=$K$2;IF(ISNUMBER(MATCH($E$2:$E$16;$J$2:$J$5;0));ROW($F$2:$F$16))));ROWS($L$2:$L2))-1);"")