Lookup data out of a group of cells

[blimes]

	a	b	c	d	e	f	g	h	i
1	Tom	21			matt	larry	randy	tom	135
2	Bill	12
3	john	23
4	Larry	76
5	Randy	32
6	Lebron	15
7	Stan	22
8	Ben	31
9	Matt	6

This is a small sample of the data i have in column a and b there may be 250 entries and the same in column e through h. I am wanting to add up the data from column b based on the entries in row e1 through i1. What would be the fastest formula to look up the values of matt(6)+larry(76)+Randy(32)+Tom(21) for a total of 135

Thanks
Adam

 

[KevCarter]

How about:

Book1
	A	B	C	D	E	F	G	H	I
1	Tom	21			Matt	Larry	Randy	Tom	135
2	Bill	12
3	john	23
4	Larry	76
5	Randy	32
6	Lebron	15
7	Stan	22
8	Ben	31
9	Matt	6
Sheet1

Cell Formulas
Range	Formula
I1	I1	=SUM(SUMIFS(B1:B9,A1:A9,E1:H1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

What would be the fastest formula ...

It depends on your Excel version. Please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using. (Don’t forget to scroll down & ‘Save’)

If you have Excel 365, you could use this.

20 12 26.xlsm
	A	B	C	D	E	F	G	H	I
1	Tom	21			matt	larry	randy	tom	135
2	Bill	12
3	john	23
4	Larry	76
5	Randy	32
6	Lebron	15
7	Stan	22
8	Ben	31
9	Matt	6
Sheet2 (2)

Cell Formulas
Range	Formula
I1	I1	=SUM(FILTER(B1:B9,ISNUMBER(MATCH(A1:A9,E1:H1,0))))

 

[blimes]

Perfect thank you much for all the help

 

[Peter_SSs]

Glad you got a successful outcome. Thanks for letting us know.
.. and for updating your profile.