Lookup from table

[freedox]

Hi, I'm trying to figure out how to make this small formula to work. I can make it work with vlookup but it means that for each colour I need to give colour number.

So in List Tab 1 I have:

Tab1.JPG

On Tab 2:

Tab2.JPG

So I need formula to look up for matching colour name in both B and C columns on Tab 1 and return colour code from Tab 2. If colour is not found, return PLEASE SELECT COLOUR or similar.

Anyone have idea how to achieve this?

[NeedForExcel]

What is 900,1000 etc?

Also, I request you to 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.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[freedox]

Sorry, so I have added a sample sheet with what it should look like.

900, 1100,1200, 1300 are the colour codes, so formula should match table header (from tab 2) to the colour in in Tab 1 colums B and C and return the colour code from Tab 2 to the Tab 1 column A

Please find sample file attached.

sample.xlsx

Before:
Tab1.JPG

After:
after.JPG

[NeedForExcel]

What if B & C columns have different data?

For example row 30?

What should be returned in Column A?

[freedox]

Needs to return "Please Select Colour" if colour doesn't exist in Tab 2. It also needs to check in both B & C columns as sometimes can column B contain for example Black and sometimes C