Array Offset() formula with height of 1 returns duplicates?

[Uhl]

Is the Offset() function with a height of 1 supposed to return
duplicate values?

Here's an example starting at cell "A1". The first column has values
"Cat", "Dog", etc.... The second column has the array formula
"{=OFFSET($A$1,0,0,2)}".

Cat Cat
Dog Dog
Bird #N/A
Plane #N/A

That's correct. But when I change the Offset() function's HEIGHT to
"1", as in "{=OFFSET($A$1,0,0,1)}", I get...

Cat Cat
Dog Cat
Bird Cat
Plane Cat

What I really want is:

Cat Cat
Dog #N/A
Bird #N/A
Plane #N/A

What am I doing wrong?

 

[Guest]

I think that it is returning a single cell so therefore excel can assign the
single cell answer to all the cells.

whereas with heights more than one it returns an array that fills the
respective cells.

What are you trying to so and maybe somebody can provided an answer.

 

[Bob Phillips]

I don't understand why you want that result, but you can get it with this
simple formula in B1 and copied down

=IF(A1=$A$1,$A$1,NA())

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Uhl]

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!

 

[Bob Phillips]

How about showing us the other formulae, start from the beginning?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Uhl]

Bob, I emailed you a sample of my spreadsheet, so be on the lookout.
Sometimes spam controls can be too stringent.

For everyone else, here's another attempt to explain my dilemma. But
this time I'll try to describe my ultimate goal.

I have an ordered list that looks like this (notice some repeated
dates):

Date Value
1/1/2006 22
1/2/2006 15
1/2/2006 10
2/5/2006 31
2/7/2006 24
2/8/2006 45
2/10/2006 37
2/12/2006 31
2/12/2006 22
2/18/2006 17
.... etc ...

I would like to enter a date, then have Excel return only the rows that
match that date.

For example, if I enter: 1/2/2006

I want:
1/2/2006 15
1/2/2006 10

I know that I'll never have more than 12 entries for a single date, so
created a 12-row table using the array/offset formula in my original
post. Because the Array formula spans all 12 rows, I get the following
results:

1/2/2006 15
1/2/2006 10
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A
#N/A #N/A

But this works out fine since line charts don't graph #N/A values. So
everything worked out perfect, unless I enter a date with a single
entry.

Entering this date: 1/1/2006

I get:
1/1/2006 22
1/1/2006 22
1/1/2006 22
.... etc ...repeated 12 times!

So how else could I accomplish this?

DISCLAIMER: This example is also slightly simplified, but the main
difference is I'm returning more than two columns for each row. It
would be very difficult and time consuming to describe my data in
intimate detail, especially in a text-based medium like a newsgroup.

Thanks!

 

[RagDyer]

Say your datalist is in A1 to B100, and the criteria date is entered in C1.

Try this *array* formula in D1:

=IF(COUNTIF(A$1:A$100,C$1)>=ROWS($1:1),INDEX(B$1:B$1000,SMALL(IF(A$1:A$100=C$1,ROW($1:$100)),ROWS($1:1))),"")

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

*AFTER* the CSE entry, copy the formula down 12 rows.

When the formula runs out of data to return, you'll get blank rows.
If you really want the #N/A error, just *replace* the quotes at the end of
the formula with NA().

 

[Uhl]

It took me a while to get it implemented, but it worked!

I got a little hung up because a couple of the references needed a
change in their "absoluteness" (if that's a word). For example,
A$1:A$100 should have been $A$1:$A$100 in two spots. And all references
to C$1 needed to be $C$1. But I figured it out.

I never would have come up with this formula by myself!?!

Thanks a ton!

 

[RagDyeR]

The formula was configured to be copied *down* a column, into 12 rows, as
your post mentioned.
And considering it was so large to start, I didn't see a need to add
unnecessary (in my mind) characters.

Appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

It took me a while to get it implemented, but it worked!

I got a little hung up because a couple of the references needed a
change in their "absoluteness" (if that's a word). For example,
A$1:A$100 should have been $A$1:$A$100 in two spots. And all references
to C$1 needed to be $C$1. But I figured it out.

I never would have come up with this formula by myself!?!

Thanks a ton!

 

[Uhl]

Oops, I meant *column* references! I guess I should have mentioned that
I was copying the formula *across* columns as well. Anyway, it was a
long day so my brain was a little hazy when I was working on this.

Thanks again!