Number Lookup in Matrix

In article <1120236668.473621.178400@z14g2000cwz.googlegroups.com>,
"Harlan Grove" <hrlngrv@aol.com> wrote:

> The volatile OFFSET call isn't needed for this. It could be done with
>
> =IF(ISNUMBER(1/COUNTIF(INDEX($A$2:$D$6000,0,MATCH(F1,$A$1:$D$1,0)),F2))
> ,"Y","N")
>
> Another advantage is that if rows with new phone numbers were inserted
> between rows 2 and 6000, the range reference in the INDEX formula will
> automatically expand to include them. The OFFSET formula would require
> manually changing the 6000 figures.

Or, you can eliminate the ISNUMBER function...

=IF(COUNTIF(INDEX($A$2:$D$6000,0,MATCH(F1,$A$1:$D$1,0)),F2)>0,"Y","N")

Domenic wrote...
....
>Or, you can eliminate the ISNUMBER function...
>
>=IF(COUNTIF(INDEX($A$2:$D$6000,0,MATCH(F1,$A$1:$D$1,0)),F2)>0,"Y","N")

And what does this return when the area code in F1 doesn't appear in
A1:D1?

In article <1120246591.296457.157290@g47g2000cwa.googlegroups.com>,
"Harlan Grove" <hrlngrv@aol.com> wrote:

> Domenic wrote...
> ...
> >Or, you can eliminate the ISNUMBER function...
> >
> >=IF(COUNTIF(INDEX($A$2:$D$6000,0,MATCH(F1,$A$1:$D$1,0)),F2)>0,"Y","N")
>
> And what does this return when the area code in F1 doesn't appear in
> A1:D1?

Ahhh yes! Thanks Harlan!