#### mspincus

##### New Member
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

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. )

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

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.

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:
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

Replies
3
Views
152
Replies
1
Views
107
Replies
2
Views
291
Replies
6
Views
171
Replies
4
Views
366

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

### 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.

### Which adblocker are you using?

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

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