Aggregate Function on Vlookup Array Not Working

mforness

New Member
Joined
Dec 15, 2004
Messages
16
I am having problems getting a complex formula to work correctly and would appreciate any help or suggestions! I am using Excel 2003 and Windows XP.

I am trying to create a single cell formula that returns the min value of an array returned from a vlookup function. This is part of a more complex solution that I am trying to implement, but I think I have narrowed my problem down to this issue, so I have created a very simple example to demonstrate.

Cells A1 to B5 contain a lookup table:
<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>
<table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Letter</th><th>Number</th></tr> <tr><td>D</td><td>4</td></tr> <tr><td>C</td><td>3</td></tr> <tr><td>B</td><td>2</td></tr> <tr><td>A</td><td>1</td></tr></table>

Cells A7 to C8 contain the input values:
<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>
<table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Lookup1</th><th>Lookup2</th><th>Lookup3</th></tr> <tr><td>D</td><td>B</td><td>A</td></tr></table>

Enter the following formula (as an array formula) into 3 cells (they need to be in one row to work, not one column):
=VLOOKUP($A$8:$C$8,$A$1:$B$5,2,FALSE)

The correct array of 4,2,1 is returned into those 3 cells (essentially the result of 3 separate vlookups), so that part seems to work. However (and here is my issue), if I enter the following formula into 1 cell (as an array formula again) to find the min value of that array:
=MIN(VLOOKUP($A$8:$C$8,$A$1:$B$5,2,FALSE))

The value of 4 is returned instead of the value of 1 that I expected. It seems like the min formula always only looks at the first value in the array instead of finding the actual min of all values. However, if the same min array formula is entered as an array formula into more than one cell, the correct min value of 1 is returned to all the cells. Since the min function is an aggregate that should only return one value, I would have expected it to work correctly when entered into only 1 cell?? I tested with other aggregates (max and count) with the same results. If the array formula is entered into 1 cell, the aggregate portion only uses the first value of the array, if it is entered into 2 (or more) it correctly uses ALL values in the array.

I can't seem to find a way to get it to work. I have tried transposing different parts of the formula, tried switching to index/match or offset/match formulas instead of vlookup... all with no success. Any ideas on why the formula isn't working? Or suggestions on a different approach to reach the same goal? Note that I am trying to keep this to a single cell solution without creating a lot of extra tables since the real data is a lot larger and the range sizes will change. And I am hoping to avoid writing a custom VBA function.

Thanks!
 
Sorry about that, I misunderstood. This is what the plant and customer matrix (A39 to D44) should be:
<STYLE type=text/css>
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</STYLE>
<TABLE class=tableizer-table>
<TBODY><TR class=tableizer-firstrow><TH></TH><TH>P1</TH><TH>P2</TH><TH>P3</TH></TR><TR><TD>C1</TD><TD>17</TD><TD>19</TD><TD>14</TD></TR><TR><TD>C2</TD><TD>19</TD><TD>15</TD><TD>16</TD></TR><TR><TD>C3</TD><TD>13</TD><TD>15</TD><TD>20</TD></TR><TR><TD>C4</TD><TD>15</TD><TD>17</TD><TD>22</TD></TR><TR><TD>C5</TD><TD>17</TD><TD>19</TD><TD>22</TD></TR>
</TABLE>

Looking at one of the cells in the matrix in detail, B40 (P1 and C1 in the matrix) is 17. Manually done out the long way, that is because 17 is the minimum value of all possible transportation options from P1 to C1 (plant to warehouse cost plus warehouse to customer cost):

P1_W1 plus W1_C1 = 17
P1_W2 plus W2_C1 = 23
P1_W3 plus W3_C1 is not a valid option (W3_C1 doesn't exist in the cost table)
P1_W4 plus W4_C1 = 24

Thanks again for your help!!

A39:D44...

<TABLE style="WIDTH: 277pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=370 x:str><COLGROUP><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5229" width=143><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3620" width=99><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 107pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 width=143> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 74pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=99>P1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64>P2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64>P3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 107pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=143>C1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=99 align=right x:num>17</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 align=right x:num>19</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 align=right x:num>14</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 107pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=143>C2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=99 align=right x:num>19</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 align=right x:num>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 align=right x:num>16</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 107pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=143>C3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=99 align=right x:num>13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 align=right x:num>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 align=right x:num>20</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 107pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=143>C4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=99 align=right x:num>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 align=right x:num>17</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 align=right x:num>22</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 107pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=143>C5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 74pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=99 align=right x:num>17</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 align=right x:num>19</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 align=right x:num>22</TD></TR></TBODY></TABLE>

B40:

Control+shift+enter, not just enter...
Code:
=SMALL(
   SUMIF($C$2:$C$31,B$39&"_"&$A$35:$D$35,$D$2:$D$31)+
   SUMIF($C$2:$C$31,$A$35:$D$35&"_"&$A40,$D$2:$D$31),
   SUM(IF(SUMIF($C$2:$C$31,B$39&"_"&$A$35:$D$35,$D$2:$D$31)*
      SUMIF($C$2:$C$31,$A$35:$D$35&"_"&$A40,$D$2:$D$31),0,1))+1)
and copy across and down.

If you would install the free morefunc.xll add-in...

Control+shift+enter, not just enter...
Code:
=SMALL(
   SETV(SUMIF($C$2:$C$31,B$39&"_"&$A$35:$D$35,$D$2:$D$31),1)+
   SETV(SUMIF($C$2:$C$31,$A$35:$D$35&"_"&$A40,$D$2:$D$31),2),
     SUM(IF(GETV(1)*GETV(2),0,1))+1)
Again copied across then down.
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Aladin - you truly are an Excel master! Thanks for your help and time with this issue.

I modified your formula a little (explained below). I have tested it out in the actual workbook with real data and it works great. I will explain my final solution in case it helps anyone else out.

Your formula worked perfectly with the test data I provided. It returns the array of costs for both transportation legs, sums then, and chooses the smallest value. If a value wasn't found in the lookup table for an option, the formula corrects by increasing the nth value that the small function returns.

The only issue is that in the larger data set it is possible to run into a situation where a valid combination (with both transportation legs in the lookup table) has a total cost less than an invalid combination (with only 1 - high cost - transportation leg in the lookup table). In this case, your formula will notice that there was an invalid combination and will return the 2nd smallest value - which is actually the invalid combination - instead of the smallest.

I modified the formula so that instead of finding the nth smallest value to account for invalid combinations, it just adds a large penalty to invalid combinations. The penalty is added by using countif to test if the combination exists in the lookup table. If it doesn't exist, 9999 is added to the zero cost that is returned. Here is the updated formula (entered in cell B40 as an array with Ctrl+Shift+Enter):

=MIN((SUMIF($C$2:$C$31,B$39&"_"&$A$35:$D$35,$D$2:$D$31)+IF(COUNTIF($C$2:$C$31,B$39&"_"&$A$35:$D$35)=0,9999,0))+(SUMIF($C$2:$C$31,$A$35:$D$35&"_"&$A40,$D$2:$D$31)+IF(COUNTIF($C$2:$C$31,$A$35:$D$35&"_"&$A40)=0,9999,0)))

Thanks again for your help Aladin. Couldn't have figured this one out without you.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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
Back
Top