Index formula

[josros60]

Hi,

i have this table and this formula,

=INDEX(Sheet1!D2:D4,MATCH(Sheet1!C2,Sheet1!C2:C4,0)*MATCH(Sheet1!A2,Sheet1!A2:A4,0))

[TABLE="width: 244"]
<tbody>[TR]
[TD="width: 61"]ID[/TD]
[TD="width: 61"]DESC[/TD]
[TD="width: 61"]A/C #[/TD]
[TD="width: 61"]AMT[/TD]
[/TR]
[TR]
[TD="align: right"]133888[/TD]
[TD]VOICE[/TD]
[TD="align: right"]8883[/TD]
[TD="align: right"]545[/TD]
[/TR]
[TR]
[TD="align: right"]100300[/TD]
[TD]IP[/TD]
[TD="align: right"]9833[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD="align: right"]100358[/TD]
[TD="colspan: 2"]INTERNET[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

how can modify it that if a/c # is blank still get the amount base on id column #.

thanks,

 

[Eric W]

I don't think that formula will do what you want. See if this does, where 1234 is your base amount.

Book1
	A	B	C	D	E	F	G	H
1	ID	DESC	A/C #	AMT		ID	A/C #	AMT
2	133888	VOICE	8883	545		100358	7777	1234
3	100300	IP	9833	800		133888	8883	545
4	100358	INTERNET		#N/A		100300	9833	800
Sheet1

Cell Formulas
Range	Formula
H2	=IFERROR(1/(1/(SUMIFS(D:D,A:A,F2,C:C,G2))),1234)