3 way lookup, two arguments should be met and throw another back

[Skogsborg]

Hi,

I have been struggeling all day with a problem. I have two sheets with thousands of rows that I need to match together. There is one number that is the same in both sheets (Part). I have tried vlookup with nested functions and index and match. I'm new to index and match and don't get how to do it.

Problem:

I want the codes to be automatically filled below the subparts box/panel/piston/rubber/ruler. The combining link is the part. The subarts can come up many times. But the combination part&subpart is unique meaning that combining them will give the key to throw back the code corresponding to this.

In other words. I want the parts from right table to find the combination of part and subpart in left table and give back barcode and it should come into corresponding subpart in right table.
I'm stuck and dont know where to start.3waylookup.xlsx

Anyone that can give at least the direction of how to continue?

Thanks,

Christer

Part Subparts Code Box Panel Piston Rubber Ruler
Part 1 Box y1 Part 1
Part 1 Panel r4 Part 2
Part 1 Piston t6 Part 3
Part 2 Box u7 Part 4
Part 2 Piston 3u Part 5
Part 2 Rubber j8 Above I want the codes to come
Part 3 Rubber h5
Part 3 Piston w2
Part 3 Ruler v5
Part 4 Panel c3
Part 4 Rubber x4
Part 4 Ruler z8
Part 5 Box i8
Part 5 Piston p4
Part 5 Panel 2h

[Melvinrobb]

By adding a helper column, would the attached work?

[Lemice]

Hello,

You can achieve this by using Array formula alone.
See the attached file.

[Skogsborg]

Thank you guys. Both ways work. I had some big struggles before I got any of them to work. You (me) need to be careful where you put youy referencing $ and not forgetting the {} for array.

But now it works fine.

Melvinrobb solution is easier to work with and understand. However the Lemice solution is nicer. I think I will use them both.

Thanks again an totally amazing that you guys can help me sitting in the other end of the world.

Christer