#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,101
Default Index Function

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$500 0,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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default Index Function

"Mike" wrote...
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$500 0,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.


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default Index Function

"Harlan Grove" wrote...
....
. . . 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)))


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Index function altah Excel Worksheet Functions 7 December 13th 06 07:03 PM
Index Function SJT Excel Discussion (Misc queries) 3 November 9th 06 03:54 AM
Index Function/Match Function M Moore Excel Discussion (Misc queries) 3 September 3rd 06 11:49 AM
If function using Index dbl Excel Worksheet Functions 13 August 9th 05 06:35 PM
Emulate Index/Match combo function w/ VBA custom function Spencer Hutton Excel Worksheet Functions 2 May 2nd 05 05:26 PM


All times are GMT +1. The time now is 07:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"