Return Offset value from a matched lookup

[JeffGrant]

Hi All,

What I would like to achieve is this.

Columns A,B & C are exported from an accounting packaging in Australian called MYOB.

Column E comes from another spreadsheet which was used to for counting stock on hand.

I need the value in column E to match column A, then return the offset value 3 rows down, 2 columns across.

as you can see from attempt, the value in E2 returns the value in F2 correctly. But the next two values in column E don't work. I suspect I should be using Index or Xlookup, but I'm not having any success.

Any assistance is great apprecaited.

ITEM.TXT
	A	B	C	D	E	F
1	Location ID	Location Name	On Hand		Lookup Code	Lookup QTY
2	01.09.02.01	BEATER SPACER - Technogel			01.09.02.01	4
3	Q S/Parts	IGC QLD Spare Parts Upstairs	0		01.09.02.02	0
4	Q Showroom	IGC QLD Showroom	0		01.09.02.03	0
5	Q W'house	IGC QLD Warehouse	3			#N/A
6		Total:	4			#N/A
7	01.09.02.02	BEATER SCRAPER - Technogel				#N/A
8	Q S/Parts	IGC QLD Spare Parts Upstairs	0			#N/A
9	Q Showroom	IGC QLD Showroom	0			#N/A
10	Q W'house	IGC QLD Warehouse	1			#N/A
11		Total:	1			#N/A
12	01.09.02.04	Rear Beater Seal - Technogel				#N/A
13	Q S/Parts	IGC QLD Spare Parts Upstairs	0			#N/A
14	Q Showroom	IGC QLD Showroom	0			#N/A
15	Q W'house	IGC QLD Warehouse	13			#N/A
16		Total:	13			#N/A
17	01.09.02.06	DOWEL PAD PIN - Technogel				#N/A
18	Q S/Parts	IGC QLD Spare Parts Upstairs	0			#N/A
19	Q Showroom	IGC QLD Showroom	0			#N/A
20	Q W'house	IGC QLD Warehouse	3			#N/A
21		Total:	3			#N/A
22	01.09.02.07	O’Ring Extraction Door				#N/A
23	Q S/Parts	IGC QLD Spare Parts Upstairs	0			#N/A
24	Q Showroom	IGC QLD Showroom	0			#N/A
25	Q W'house	IGC QLD Warehouse	1			#N/A
26		Total:	1			#N/A
27	01.09.02.08	BEATER SCRAPER - Technogel				#N/A
28	Q S/Parts	IGC QLD Spare Parts Upstairs	0			#N/A
29	Q Showroom	IGC QLD Showroom	0			#N/A
30	Q W'house	IGC QLD Warehouse	1			#N/A
31		Total:	1			#N/A
32	022881	Inline Power Filter 1A				#N/A
33	Q S/Parts	IGC QLD Spare Parts Upstairs	0			#N/A
34	Q Showroom	IGC QLD Showroom	0			#N/A
35	Q W'house	IGC QLD Warehouse	0			#N/A
36		Total:	0			#N/A
Sheet1

Cell Formulas
Range	Formula
F2:F29	F2	=OFFSET(A2,MATCH(E2,$A$2:$A$392,TRUE)+3,2)
F30:F36	F30	=OFFSET(A30,MATCH(E30,A30:A420,TRUE)+3,2)

ITEM.TXT
	A	B	C	D	E	F
1	Location ID	Location Name	On Hand		Lookup Code	Lookup QTY
2	01.09.02.01	BEATER SPACER - Technogel			01.09.02.01	4
3	Q S/Parts	IGC QLD Spare Parts Upstairs	0		01.09.02.02	0
4	Q Showroom	IGC QLD Showroom	0		01.09.02.03	0
5	Q W'house	IGC QLD Warehouse	3			#N/A
6		Total:	4			#N/A
7	01.09.02.02	BEATER SCRAPER - Technogel				#N/A
8	Q S/Parts	IGC QLD Spare Parts Upstairs	0			#N/A
9	Q Showroom	IGC QLD Showroom	0			#N/A
10	Q W'house	IGC QLD Warehouse	1			#N/A
11		Total:	1			#N/A
12	01.09.02.04	Rear Beater Seal - Technogel				#N/A
13	Q S/Parts	IGC QLD Spare Parts Upstairs	0			#N/A
14	Q Showroom	IGC QLD Showroom	0			#N/A
15	Q W'house	IGC QLD Warehouse	13			#N/A
16		Total:	13			#N/A
17	01.09.02.06	DOWEL PAD PIN - Technogel				#N/A
18	Q S/Parts	IGC QLD Spare Parts Upstairs	0			#N/A
19	Q Showroom	IGC QLD Showroom	0			#N/A
20	Q W'house	IGC QLD Warehouse	3			#N/A
21		Total:	3			#N/A
22	01.09.02.07	O’Ring Extraction Door				#N/A
23	Q S/Parts	IGC QLD Spare Parts Upstairs	0			#N/A
24	Q Showroom	IGC QLD Showroom	0			#N/A
25	Q W'house	IGC QLD Warehouse	1			#N/A
26		Total:	1			#N/A
27	01.09.02.08	BEATER SCRAPER - Technogel				#N/A
28	Q S/Parts	IGC QLD Spare Parts Upstairs	0			#N/A
29	Q Showroom	IGC QLD Showroom	0			#N/A
30	Q W'house	IGC QLD Warehouse	1			#N/A
31		Total:	1			#N/A
32	022881	Inline Power Filter 1A				#N/A
33	Q S/Parts	IGC QLD Spare Parts Upstairs	0			#N/A
34	Q Showroom	IGC QLD Showroom	0			#N/A
35	Q W'house	IGC QLD Warehouse	0			#N/A
36		Total:	0			#N/A
Sheet1

Cell Formulas
Range	Formula
F2:F29	F2	=OFFSET(A2,MATCH(E2,$A$2:$A$392,TRUE)+3,2)
F30:F36	F30	=OFFSET(A30,MATCH(E30,A30:A420,TRUE)+3,2)

 

[JeffGrant]

Saba, how do i use PQ to change the format?

I have created a query, and produced the table,

The querry is Table.TransformColumnTypes(Source,{{"Location ID", type text}, {"Location Name", type text}, {"On Hand", Int64.Type}})

But it still fails. I guess it is because excel is formating the columns different?

 

[JeffGrant]

Hi Saba. Found it. MYOB is export a carriage return the Excel is picking up.