listing array values in a dynamic list based on 2 criteria from ranges

Brand new here, hopefully you guys can point me in the right direction. I have a list of items, 1 thru 12, that have a High or Low Profit and a High or Low Mix.

I'd like to 'graph' these in a quadrant style layout depicting items in a dynamic list in the 4 possibilities of High-High, High-Low, Low-High, and Low-Low.

I've tried index(small(if(row from http://www.exceltactics.com/make-fil...using-small/5/ and while that works great if i'm just testing for one criteria, it falls apart if i put an AND in there for testing both.

I've tried Choose/Match from http://www.excelguru.ca/forums/showt...met-excel-2010 that it just doesn't look like that one will work for me.

I'm sure i'm missing something small.

I attached some pics of what i'm working with, hopefully it makes sense!

thanks guys!

Hi there... and welcome to the Excel Forum. You have attached an image. That's not very easy to work with. We have to re-type your information before we can begin to address your problem. That will put a lot of people off completely. Not only that, but (depending on their choice of browser) many members cannot even see the image.

Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

However, nothing on this Forum works quite as well as you might expect. The attachment icon doesn't work!! (Helpful, isn't it!!). Instead, just before you submit your post, click on GO ADVANCED (near the bottom) and then scroll down to Manage Attachments to open the upload window. The relevant instructions are at the top of that screen.

It's a browser thing. Depending on the browser, you may see nothing... However, the main issue was my unwillingness to retype everything before starting!! I made a minor adjustment to your layout, to make life simpler.

In D3, use this array formula:

Formula:

Please Login or Register  to view this content.

adjusted as shown in the sheet for the other quadrants.

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

What a total plonker. Me, that is. Wrong file attached!!!! I was in a rush to shut the PC down and attached an answer to someone else's problem....

try this:

Great! I'm glad to have helped! If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Cheers! Any time.