Index / Match Help
 

I am using the following formula to grab a specific amount from another Excel
worksheet. Can't use the SUMPRODUCT because I want the value a line down from
where it finds CSFS Totals.

=(INDEX('C:\Trend Cards\All New Renewed\[April New
Renew.xls]Sheet1'!$H$3:$H$5000,MATCH("CSFS Totals",'C:\Trend Cards\All New
Renewed\[April New Renew.xls]Sheet1'!$B$3:$B$5000,0)+1))/1000

This value will not always show up on the other Excel file, which makes it
display a #N/A. Is there anyway for it to display a 0 instead when it can't
find it? Any help would be appreciated. Thanks!

Index / Match Help
 

Hi Jasmine,

Try this:
=IF(ISNA(MATCH("CSFS Totals",'C:\Trend Cards\All New
Renewed\[April New Renew.xls]Sheet1'!$B$3:$B$5000,0)),0,(INDEX('C:\Trend
Cards\All New Renewed\[April New
Renew.xls]Sheet1'!$H$3:$H$5000,MATCH("CSFS Totals",'C:\Trend Cards\All New
Renewed\[April New Renew.xls]Sheet1'!$B$3:$B$5000,0)+1))/1000)
Basically I just added the IF and ISNA function to your original formula.
This should change any #N/A to 0. Hope this helps.

-Simon

"Jasmine" wrote:

I am using the following formula to grab a specific amount from another Excel
worksheet. Can't use the SUMPRODUCT because I want the value a line down from
where it finds CSFS Totals.

=(INDEX('C:\Trend Cards\All New Renewed\[April New
Renew.xls]Sheet1'!$H$3:$H$5000,MATCH("CSFS Totals",'C:\Trend Cards\All New
Renewed\[April New Renew.xls]Sheet1'!$B$3:$B$5000,0)+1))/1000

This value will not always show up on the other Excel file, which makes it
display a #N/A. Is there anyway for it to display a 0 instead when it can't
find it? Any help would be appreciated. Thanks!

Index / Match Help
 

Jasmine,


=IF(ISNA((INDEX('C:\Trend Cards\All New Renewed\[April New
Renew.xls]Sheet1'!$H$3:$H$5000,MATCH("CSFS Totals",'C:\Trend Cards\All
New
Renewed\[April New Renew.xls]Sheet1'!$B$3:$B$5000,0)+1))/1000),0,
(INDEX('C:\Trend Cards\All New Renewed\[April New
Renew.xls]Sheet1'!$H$3:$H$5000,MATCH("CSFS Totals",'C:\Trend Cards\All
New
Renewed\[April New Renew.xls]Sheet1'!$B$3:$B$5000,0)+1))/1000)

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=547186

Index / Match Help
 

That works perfectly! I am not exactly sure I understand why, but thank you
very much! That solves a huge problem for me.

"SiC" wrote:

Hi Jasmine,

Try this:
=IF(ISNA(MATCH("CSFS Totals",'C:\Trend Cards\All New
Renewed\[April New Renew.xls]Sheet1'!$B$3:$B$5000,0)),0,(INDEX('C:\Trend
Cards\All New Renewed\[April New
Renew.xls]Sheet1'!$H$3:$H$5000,MATCH("CSFS Totals",'C:\Trend Cards\All New
Renewed\[April New Renew.xls]Sheet1'!$B$3:$B$5000,0)+1))/1000)
Basically I just added the IF and ISNA function to your original formula.
This should change any #N/A to 0. Hope this helps.

-Simon

"Jasmine" wrote:

I am using the following formula to grab a specific amount from another Excel
worksheet. Can't use the SUMPRODUCT because I want the value a line down from
where it finds CSFS Totals.

=(INDEX('C:\Trend Cards\All New Renewed\[April New
Renew.xls]Sheet1'!$H$3:$H$5000,MATCH("CSFS Totals",'C:\Trend Cards\All New
Renewed\[April New Renew.xls]Sheet1'!$B$3:$B$5000,0)+1))/1000

This value will not always show up on the other Excel file, which makes it
display a #N/A. Is there anyway for it to display a 0 instead when it can't
find it? Any help would be appreciated. Thanks!