hiding #N/A

  • Thread starter Thread starter MeAgain
  • Start date Start date
M

MeAgain

I am using this formula
=INDEX(BankAccount!F:F,MATCH(E42,BankAccount!F:F,0),0)
it works fine for me but as I copied it down so when there is no value to
look for(ie in E42) it returns the #N/A.

how can I get rid of it?
thanks
 
thanks
There is another one.
here is the scenario.
sheet TAccount

TESCO
Date Dr Cr
22-10-03 (Formula here)
27-10-03 (Formula here)

SHEET Purchases

Date Supplier Total
22-10-03 TESCO 1503.49
23-10-03 ASDA 230.23
24-10-03 etc 23.20
27-10-03 TESCO 990.38

I need a formula to return the value for Dr column in TAccount sheet from
the purchases sheet like IF there is TESCO and date 22-10-03 in the same row
get Total from column 3.
so the result would be 1503.49 ELSE 0 and so on
thanks
 
One way:

=SUMPRODUCT(--(Purchases!$A$1:$A$1000=A3),
--(Purchases!$B$1:$B$1000=$A$1), Purchases!$C$1:$C$1000)

another (array entered: CTRL-SHIFT-ENTER or CMD-RETURN):

B3: =IF(ISNA(MATCH(A3&$A$1,Purchases!A1:A1000 &
Purchases!B1:B1000,FALSE)), 0,
INDEX(Purchases!$C$1:$C$1000,MATCH(A3&$A$1,Purchases!$A$1:$A$1000 &
Purchases!$B$1:$B$1000,FALSE)))

Where A1 contains the Supplier name.
 
thanks
the sumproduct one worked for me.
I don't know why the other one not working but hey thanks again
 
Back
Top