View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Uhl Uhl is offline
external usenet poster
 
Posts: 7
Default Array Offset() formula with height of 1 returns duplicates?

I thought I was being helpful by simplifying the example. But looking
at it at face value, I guess I can understand why you'd say, "Why would
you want to do *that*!" Oh well, so much for simplicity! ;-)

Here's more detail...

I'm using the Offset() array formula to return a set of rows from
another table. Since the table is ordered, but may have multiples of a
single entry, I'm using Match() and Countif() to determine the starting
row and the "height" for a specific value. In the Offset() formula, I
use the results from Match() and Countif() as the "rows" and "height"
parameters, respectively. So depending on the specific value I'm
looking up, the results may be no rows, one row, or multiple rows.

Basically, I am...
1) looking up a value in a table
2) identifying the starting row
3) determining how many rows match
4) returning an array for the rows that match

I'm familiar with Vlookup() but since I need multiple rows returned, I
couldn't figure out a way to use it. Like I said in my original post,
the only problem is when HEIGHT=1, when I get multiple, duplicate rows.
Seems like I'm so close!