About Us

Dave Peterson

What was the formula you tried that didn't work?

wrote:

Dave, your quick reply's are great! Thanks!!

I don't think the Index and Match formula below will work. I thank you
for your patience and if you have more, I'll try to explain what I'm
doing.

In the 'Overview' tab, Column B has the date. Column C has the type of
work. Column D needs the measurement of that work.

Col B Col C Col D
Date Work AHT
Ex. 2006011 Service 310

In the 'GSO' tab, Column A has the date. Column B has the type of
work. Column E has the measurement of that work. (This sheet contains
alot of data - Column E is what we need).

Col B Col C Col E
Date Work AHT
Ex. 2006011 Service 310

Column B in both sheets contains different dates. Column C contains 5
different types of work. Column E is always numeric and changes daily.

I cannot use vlookup because I need to match the date and work in the
'Overview' tab with the date and work in the 'GSO' tab. If there was
only one field in each sheet to match, vlookup would be perfect. I
thought this could be done with sumproduct. But I do not need to sum,
count, mulitply, etc., anything. Just return the value from 'GSO' to
'Overview'.

Dave, thanks again!

Jason

Dave Peterson wrote:
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

--

Dave Peterson

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
SUMPRODUCT Criteria Via Cell Reference??	John V	Excel Worksheet Functions	8	April 12th 06 07:55 PM
SumProduct with criteria list	Sige	Excel Worksheet Functions	17	February 21st 06 02:30 PM
Sumproduct w/date criteria not working	JANA	Excel Worksheet Functions	7	April 15th 05 11:19 AM
Unique sumproduct with criteria!	Naomi	Excel Worksheet Functions	5	March 14th 05 07:01 PM
Using COUNTIF with 2 criteria - SUMPRODUCT?	Mike R.	Excel Worksheet Functions	2	February 24th 05 05:57 AM

Menu

About Us