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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

ITWare2008

Board Regular
Joined
Apr 16, 2010
Messages
174
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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);"")
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,602
Messages
5,838,321
Members
430,538
Latest member
PedroOliveira

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top