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)

 

[Saba Sabaratnam]

You could use the following formula in F2

=INDEX(C:C,MATCH(E2,A:A,0)+4)

See my spreadsheet layout for your data

Kind regards

Saba

 

[Saba Sabaratnam]

Yes I am very familiar with MYOB

 

[JeffGrant]

You could use the following formula in F2

=INDEX(C:C,MATCH(E2,A:A,0)+4)

See my spreadsheet layout for your data

View attachment 41793

Kind regards

Saba

Hi Saba,

thanks for the quick reply. When i copy the formula down I get a bunch of #N/A. Any ideas?

 

[JeffGrant]

MYOB AR is definitely not a lot of fun with giving out the data to manipulate any more

 

[Saba Sabaratnam]

No problem, it means that lookup value does not exist in Column A (that is no matching value found in A).

If the NA value is legitimate and you do not want to show these NAs, you could modify the formula as shown below.

=IFERROR(INDEX(C:C,MATCH(E2,A:A,0)+4),"")

Kind regards

Saba

 

[Saba Sabaratnam]

I use it to extract data for my analysis purpose and have to use Excel power query to get the data in the format that I want.

 

[JeffGrant]

Hi Saba, the formula works great, the problem with the format of the cell when MYOB does the export Thanks for your help

 

[Saba Sabaratnam]

No problem Jeff

 

[JeffGrant]

I am not familiar with PQ. Am looking at it now.