pricing spreadsheet

mspincus

New Member
Joined
Jul 30, 2010
Messages
37
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,))
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
This should work!!
Rich (BB code):
=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
 
Upvote 0
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. )
 
Upvote 0
Just use
Rich (BB code):
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
 
Upvote 0
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.
 
Upvote 0
How does it not work??
How is data entered in B19 and C19??
The formula does work
Rich (BB code):
=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
	
	
	
	
	
	


Rich (BB code):
=IF($B19*$C19....
lenze
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,254
Messages
6,135,490
Members
449,942
Latest member
Gitad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top