IF Column Contains Text Matching Another column then return Values from second sheet.

Hi,

I am really stuck on this one. I have a large stock sheet which one column has brand codes in it.

GCT
SCJ
CCT
HTS
ETH

These correspond to another tab on the sheet which references what these brands full names are:

GCT Grow Cube Tent
SCJ Secret Jardin Tent
CCT Clone Cube Tent
HTS HortiTechnics
ETH Edward Trimmer Hands

I need to set a column so that if the column on the main sheet contains the abbreviated code then it will return the full brand name.

excel help 1.jpgexcel help 2.JPG

I have over 30 brand names so the IF string started to become unworkablely large.

Any help greatly appreciated.

Thanks

Alex for BP Import - excel help.xlsx

Thanks for your help i have now attached the file. When I have tried your code it always gets the first brand OK but I can not copy this down to subsequent rows (1500+) I get an error. Thanks for your help,

See attached.

Thanks a lot for your help. Really appreciate it it worked flawlessly and save me a lot of time.

Lock the cell references.

=INDEX('Brand Code'!$B$3:$B$79, MATCH('Stock sheet'!$E$3:$E$1128, 'Brand Code'!$A$3:$A$79, 0))