Remember that =sumproduct() will return the sum of the matching records and if
there is only one matching record, then you're fine.

But if you want to return the first value (and that could be non-numeric)...

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

wrote:

Thanks Dave, I think you have me going in the right direction. I need
the formula to work as more of a vlookup. The reason I can't use
vlookup is because I need to match two cells in Sheet1 to two cells in
Sheet2, then return the value a couple of columns over from Sheet2 into
Sheet1.

The two cells (in separate columns) in both sheets are text and
numeric. Ex. Col A is numeric, Col B is text. Col E in Sheet 2 is
numeric.

Sheet1
Col B is the date.
Col C is the type of work.
Col D = value from Col E, Sheet2

Sheet2
Col A is the date.
Col B is the type of work.
Col E is one measurement of the type of work.

Sheet1 is an overview of the data on Sheet2. Sheet2, Col E is a
particular metric we're focusing on - which we need to pull into sheet
one Col D.

Dave, your help is appreciated!

Thanks,
Jason

Dave Peterson wrote:
=sumproduct(--(sheet2!a1:a100=a2),--(sheet2!b1:b100=b2),(e1:e100))

Will add up all the values in column E that match both column A and B.

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

========
You could use a different formula if you wanted to return just the first match
-- nice if you're retrieving text.

wrote:

I have searched and searched these groups and need to ask for
assistance. Here is a sample of my data:

Sheet 1
Col A Col B
2007011 A
2007012 B
2007013 C
2007014 D
2007021 E

Sheet 2 Col B Col E
2007011 A 300
2007012 B 310
2007013 C 320
2007014 D 310
2007021 E 320

Using the value's in Col A & B in Sheet 1, I need to return the value
in Col E in Sheet 2. I know that a vlookup will not work. Have been
experimenting w/different variations of SUMPRODUCT, but cannot get it
to work.

Any and all help will be greatly appreciated!!!

Thanks,
Jason

--

Dave Peterson

--

Dave Peterson