Find Location of Matches in an Array

[MartyCollins]

Hi there first post

Ok I have an array in one sheet.(EG 80 rows/50Columns)
EG....... it goes LOT/QTY - LOT/QTY Repeat-- I know bad design but too late to change and its not mine...

LOT	QTY	LOT	QTY	LOT	QTY
H125	10	H123	3	XYZ	9​
J124	15	ABC	9
		H125		AFF	10​
H123	9	ABC	5

In another sheet I have a simple column of LOT numbers. (an ERP download of available LOTS)
These same Lot numbers can appear many times in any location within the array above EG H123 appears twice

LOTS	Sum
H123	14​
H125	13​
J124	15​
UKG6	0​

Beside the single column of LOTS i want to write a formula that returns the sum of the QTYs to the immediate right of all instances of matches..

Looking forward to your responses.

Thanks,
MArtin.

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1	LOT	QTY	LOT	QTY	LOT	QTY
2	H125	10	H123	3	XYZ	9
3
4	J124	15	ABC	9
5			H125	13	AFF	10
6	H123	9	ABC	5
7
8
9	LOTS	Sum
10	H123	12
11	H125	13
12	J124	15
13	UKG6	0
Main

Cell Formulas
Range	Formula
B10:B13	B10	=SUMIFS(B2:F7,A2:E7,A10)

 

[MartyCollins]

Hi Fluffs,
That is just brilliant.. staggered arrays?? - Ive not seen that before.
Thanks so much!!
Ill see if i can get it work but i cant see why it wont
Brilliant,
Martin.

 

[Fluff]

You're welcome & thanks for the feedback.