Need a workable IFAND function formula for multiple criteria or maybe an Array formula?

Hi All,

I have rows of an order# in a column, rows of differing months that corresponds to the order number in another column, rows of different years that corresponds to the order# and months in another column and finally, rows of a sales percentage corresponding to the order#, months and years.

I have built a data validation list so that I can change order numbers and have the corresponding sales percentage change for the specific month and year as I change the order #'s.

The attached images shows how my data is laid out...

I have tried the following formula....

=if(and($B$2(order#)=$F$2:$F$100(range of order#'s),D4(Month)=$G$2:$G$100(range of months),C5(Year)=$H$2:$H$100(range of years),$I$2:$I$100(range of sales %),"")

This formula returns blanks for everything. I am trying to populate the second spreadsheet with the order %'s so that I can create graphs.

Any help is appreciated.

Thanks.

We cannot work with images. Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Because browsers behave differently, many of our members cannot see uploaded images. Do not upload a picture of your file as this will only delay getting solutions.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

If I am not making it clear, please let me know and I will try my best to make it easier to understand as I need assistance in figuring this out today.

Thanks.

Originally Posted by jeptik

If I am not making it clear, please let me know and I will try my best to make it easier to understand as I need assistance in figuring this out today.

Thanks.

Sorry - I didn't see that you had attached the file. Looks like John has sorted you out, though.