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:
<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
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