pricing spreadsheet

[mspincus]

I have 10 styles of furniture with 10 different variations thus 10 different pricing. I am currently using a index match formula. I allow for 5 different items to be purchased at a time thus 5 rows with the index match formula. if 2 items are purchased, it leaves 3 rows. those three rows show up with a #N/A in it. I want it to have a zero conclusion if nothing is entered . Any thoughts

=INDEX($BB$17:$BO$501,MATCH($B$19,$BA$17:$BA$501,0),MATCH($C$19,$BE$6:$BO$6,))

 

[lenze]

This should work!!

=IF($B$19,INDEX($BB$17:$BO$501,MATCH($B$19,$BA$17:$BA$501,0),MATCH($C$19,$BE$6:$BO$6,0)),0)

lenze

 

[mspincus]

you are incredible. it worked on the b19. in other words if b19 is empty, zero is in the cell. but if i add the the b collumn and the c cell, a no value comes up. We are very close. ( i guess you are very close. )

 

[lenze]

Just use

IF($B$19*$C$19,INDEX($BB$17:$BO$501,MATCH($B$19,$BA$17:$BA$501,0),MATCH($C$19,$BE$6:$BO$6,0)),0)

lenze

 

[mspincus]

still a no go. I am sorry to bother you. Thank you for your helo. I have tried to call tracy at mr excel. I do not mind paying someone. she has yet to return my call . I feel bad that you have put in so much time and effort. Maybe if i could send the file. and walk trough what you have already done.

 

[lenze]

How does it not work??
How is data entered in B19 and C19??
The formula does work

=IF($B$19*$C$19,INDEX($BB$17:$BO$501,MATCH($B$19,$BA$17:$BA$501,0),MATCH($C$19,$BE$6:$BO$6,0)),0)[/code]
The IF($B$19*$C$19 will be TRUE only when both cells have a value, ,which will then perform the Index/Match, otherwise it will return a 0!!!!
Edit: Just a thought. Remove the $ sign from the Row address so you an copy down
	
	
	
	
	
	



	
	

		
=IF($B19*$C19....
	
lenze

 

[mspincus]

still a no go. I can send you the excel doc. if you like. Again, thank you so much. My name is mark S. Pincus in dallas tx 469.222.2009

the web site is www.bocaleather.com the styles are all there