Dynamic Pivot (double vlookup) to get data

[Gexo]

I have two inputs:

Input 1

Order	Code	Type	number	data
2132342	AUS	ABC	11
12412342	USA	DEF	23
12323432	USA	DEF	22
213231	MEX	ABC	21
45345	CAN	DEF	32
345233	CAN	ABC	22
12342534	USA	DEF	12

Input 2

Code	ABC 11/12	ABC 21/22	DEF 11/12	DEF 22/23	DEF 31/32
AUS	5	5	7	1	4
USA	3	4	5	6	5
CAN	8	2	2	2	6
MEX	1	9	2	5	7

I'm trying to find a way to find the data from Input 2 using the Code, Type and number from Input 1 in order to get:

Order	Code	Type	number	data
2132342	AUS	ABC	11	5
12412342	USA	DEF	23	6
12323432	USA	DEF	22	6
213231	MEX	ABC	21	9
45345	CAN	DEF	32	6
345233	CAN	ABC	22	2
12342534	USA	DEF	12	5

Any help is highly appreciated!

[jdunkerley79 · Accepted solution]

Use a transpose tool to get column names and values from table 2 into a field.

You can then use regex to break into Type and Number

I am guessing 11/12 means 11 or 12, so I used a text to columns to break to rows

Sample attached.

[Gexo]

Thats exactly what I was trying to do.

Thank you very much for your help!