Number Lookup in Matrix

N Harkawat wrote...
>assuming that your 3 digit area code is in row 1 between A1:D1 and the phone
>number is between B1:D6000
>and "another cell" holding the area code is cell F1 and phone number in F2
>
>=IF(ISNA(INDEX(OFFSET(A2,,MATCH(F1,$A$1:$D$1,0)-1,6000),
>MATCH(F2,OFFSET(A2,,MATCH(F1,$A$1:$D$1,0)-1,6000),0))),"N","Y")
>
>will give a Y or N depending whether phne number exists
....

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.

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!