Using VLOOKUP and SUMIFS at the same time

[codemancode]

Hey guys and gals, I have a question about a formula im trying to build. I'll type an example below because im as bad at explaining as I am at writing formulas apparently...So, on Sheet1 I have the following In example data in rows:

A1 B1 C1 D1
car RV boat plane
boat car
Plane taxi boat RV


in Sheet2 I have the following in a column:

car 3
RV 4
truck 2
boat 8
taxi 5
plane 2

I would like excel to take the first row, look at sheet2, and say car+RV+boat+plane and return 15. then boat+car 11 etc. I can do a SUMIF or SUMIFS and I can use VLOOKUP, for each one separate, but i cant figure out how to make them work together for a value in the entire row. Or if those are even the best functions to use for this. Thank you very much in advance for any help!

 

[johnmpl]

Hi, codemancode!

You could try this:

Book1
	A	B
1	car	3
2	RV	4
3	truck	2
4	boat	8
5	taxi	5
6	plane	2
Sheet2

Book1
	A	B	C	D	E
1	car	RV	boat	plane	17
2	boat	car			11
3	Plane	taxi	boat	RV	15
Sheet1

Cell Formulas
Range	Formula
E1	=SUMPRODUCT(SUMIF(Sheet2!A$1:A$6,A1:D1,Sheet2!B$1:B$6))

Blessings!

 

[codemancode]

WOW! it is amazing to me how quickly other people can come up with the solution to a problem ive worked on for days hahaha. Thank you kind person, i give you all the internet dollars I learn something new every day.