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