Retriveing multiple matches based on multiple criteria

oyvimoen

New Member
Joined
Sep 30, 2014
Messages
2
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:

CodeLeft 4 codeLeft 5 codeLeft 6 codeLetter codeUnitUnit priceMatch codeMatch letterMatch unit
41.141.141.141.1Im317,6743.1Bm
42.142.142.142.1Im530,17C
42.242.242.242.2Cm662,67G
42.342.342.342.3Bm553,33H
42.342.342.342.3Cm620,33
42.442.442.442.4Bm449,67
43.1143.143.1143.11Bm75,33
43.1143.143.1143.11Hm102,17
43.1143.143.1143.11Gm102,17
43.1143.143.1143.11Fm102,17
43.1143.143.1143.11Cm76,17
43.1243.143.1243.12Bm106,33
43.1243.143.1243.12Cm107,07
43.1443.143.1443.14Cm136,97
43.3743.343.3743.37Im862,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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

ITWare2008

Board Regular
Joined
Apr 16, 2010
Messages
174
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

Board Regular
Joined
Mar 7, 2013
Messages
146
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

New Member
Joined
Sep 30, 2014
Messages
2
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

Board Regular
Joined
Mar 7, 2013
Messages
146
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.
 
Last edited:

StefanVL

Board Regular
Joined
Mar 7, 2013
Messages
146
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);"")
 

Watch MrExcel Video

Forum statistics

Threads
1,109,550
Messages
5,529,472
Members
409,884
Latest member
Msinmath
Top