Code Read

It's been a while since I've had to do anything like this but I am trying to work out a formula or something to read a string of code.

It's alpha numeric and each code means something different. The codes show up in a single cell e.g. A1 B2 C3.

I need a formula that can look at the string of text and establish that A1 is Apple, B2 is Bananas and C3 is Carrots, as an example.

I am completely stuck, any ideas?

I've attached a basic example of what I want to do

Example.xlsx

mc11,

Welcome to the forum!
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

mc11,

Attached is a modified version of your workbook. I reformatted your Key into an Excel-friendly table so that vlookups could be performed on it. Then, in cell A2 is this formula copied over and down:
=IFERROR(VLOOKUP(TRIM(MID(SUBSTITUTE($D2," ",REPT(" ",99)),MAX((MATCH(A$1,$A$1:$C$1,0)-1)*99,1),99)),$G$2:$H$10,2,FALSE),"")