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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

mspincus

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

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

mspincus

New Member
Joined
Jul 30, 2010
Messages
37

ADVERTISEMENT

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

Legend
Joined
Feb 18, 2002
Messages
13,690
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:

mspincus

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,421
Messages
5,831,520
Members
430,075
Latest member
Francis101

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
Top