Reverse lookup with only one search criteria

Hi everyone,
I have a problem which I'm sure should be simple to solve but for the life of me I can't work it out.

Below is a snippet of the workbook I'm using, I've combined parts of two sheets into one for this photo:
On the right hand side is a list of orders being exported from our site.
On the left is a table of the various product codes we are using.

All we want to do if for the cell K3 to display the correct product type based on the number in J3 and the table on the left. The amount of products will continue to expand, so it needs to be dynamic.

I've been using index & match when using a matrix and I have two search criteria, but I'm struggling to solve this with only one.

Really appreciate any help.

Thank you,

Capture.JPG

Originally Posted by Special-K

in K3
=INDEX($A$1:$C$1,AGGREGATE(15,6,(COLUMN($A$2:$C$11)-COLUMN($A$2)+1)/($A$2:$C$11=K2),1))
and copy down the column

Your actual spreadsheet contains product No.s that do not appear in the main table on the left of the spreadsheet hence the #NUM errors.
Fix that and everything should work.

Yeah sorry about that, was just a bunch of dummy data.
Thanks for the advice though mate.

Originally Posted by PaulM100

I think that this is what you are looking for: =INDEX($A$1:$C$1,SUMPRODUCT(MAX(($A$2:$C$11=$K2)*(COLUMN($A$2:$C$11))))-COLUMN($A$1)+1)
Wrap it in a iferror to get rid of errors

Cheers buddy. As soon as I get a second I'll give it a go.

Really appreciate the help.