VLOOKUP and ?

[BradleyS]

Hi

I'm a little stuck knowing how to get this to work. I have played with VLOOKUP, Match and Index and I can't seem to combine them to get the result I need.

Here is my spreadsheet:

I want the results in the green table, which looks up data in the yellow table.
PLEASE IGNORE THE COLOURS IN THE CODE COLUMN, I have only done this to help explain my issue.

BLUE COLUMN formula:
It needs to vlookup the ID in the yellow table
Match the text in the CODE column and if equal to "C10*"
Then display the text description shown in column DESC

The blue cells in the CODE column, show that they all start with C10, therefore they are all classed as the same and should appear in the blue column

ORANGE COLUMN formula:
The orange CODE is specific, i.e. MATCH if equal to 2126300

RED COLUMN formula:
The red should be treated the same as blue, but using a different text description, i.e. MATCH if equal to "9h4*"

Could someone help me put the code together to achieve this?
Thank you

 

[T. Valko]

This is absolutely fantastic.

Thank you all.

You're welcome. Thanks for the feedback!