Index Function

[Mike]

Hello,

I have a data array I am looking up data in. When I use the following
function: INDEX(Data!$N$2:$R$5000,MATCH(A52,Data!$R$1:$R$5000,0),1), the
function retruns 3 possible values: #N/A if the lookup value is not found,
the actual value I am trying to return from column 1 of the array and a 0. I
don't understand why this is returuning a 0 for some of my lookups. I checked
the help on both INdex and Match and did not see anything. I checked the
function and I don't think there is anything wrong with the setup. Has anyone
ever seen this before?

Thanks,
Mike

 

[Harlan Grove]

I have a data array I am looking up data in. When I use the following
function: INDEX(Data!$N$2:$R$5000,MATCH(A52,Data!$R$1:$R$5000,0),1), the
function retruns 3 possible values: #N/A if the lookup value is not found,
the actual value I am trying to return from column 1 of the array and a 0.
I don't understand why this is returuning a 0 for some of my lookups.

....

This may be a subtle thing like MATCH treating A52 as 0 if A52 were blank.
If there were a 0 in Data!R1:R5000, the MATCH call would find it if A52 were
blank, and if the corresponding cell in Data!N1:N5000 were 0 or blank, Excel
would return 0 from your formula. If your formula should return #N/A when
A52 is blank, use

=INDEX(Data!$N$2:$N$5000,IF(ISBLANK(A52),#N/A,
MATCH(A52,Data!$R$1:$R$5000,0)))

and also note that your MATCH call will return an index offset by 1 from
your INDEX call's 1st argument range since the former begins in row 1 while
the latter begins in row 2.

 

[Harlan Grove]

. . . If your formula should return #N/A when A52 is blank, use

=INDEX(Data!$N$2:$N$5000,IF(ISBLANK(A52),#N/A,
MATCH(A52,Data!$R$1:$R$5000,0)))

....

Let me simplify that.

=IF(ISBLANK(A52),#N/A,INDEX(Data!$N$2:$N$5000,
MATCH(A52,Data!$R$1:$R$5000,0)))