Index Match / Vlookup / Other formula needed

Hello all

I have been trawling this forum and google to try and get an answer to my problem but so far had little luck for the way I would ideally like it to work.

Untitled-2.jpg

Above is the spreadsheet I am trying to sort, I would like the desired results to show as per J3:J10. The result depends on the yellow box (J1) to correspond to row 2 and also only display the colors in column B if there is a "O" in the column that matches J1.

I also don't want any empty cells when colors aren't available.

This might be something very simple but i'm not sure of the terminology to find the right answer.

Thank you in advance for taking the time to look.

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

Hi davsth

That formula works thank you. I don't need the drop downs as the result will come from other sheets so this formula is exactly what I was after. Thank you so much.