How best to...?

[Trixie]

Why am I thinking of work stuff on the weekend?!?

I'm hoping someone out there has something to teach a newer user. Thi
is a super group that seems to be very patient.

*Sheet1*: I have a table that is 6x19 - $K$8:$P$26. There could b
values in any of the fields, and their location within the table coul
be anywhere.

K|L|M|N|O|P
OPB|OPC|IPC|OFA|OFB|810
IPA|OFE|IPD||876
OPA|OPD|IPE||878
IPB|IFE|IFD||804

The 3 digit alpha values would not be duplicated in $K$8:$O$26.

*Sheet2*: I need to take the value of a cell (say R119 is OFE, althoug
there will be a number of them throughout Sheet2) find a match in Sheet
$K$8:$O$26 ($L$9) of the and return a corresponding value from Sheet
$P$8:$P$26 ($P$9 - 876) that is on the same row as the Sheet2 R11
value.

Vlookup is really the only function I have used up until now, but
tried to pull some formulas together after viewing the Contextures sit
(probably even some really creative, never before tried combination
:Bgr ) using Index, Match, Vlookup & derivatives of, but have not bee
successful. Unless my lookup value is in column K, which then return
my numeric value.

Any insight would be greatly appreciate...my Excel VBA for dummies i
on order at my local bookstore. I am not necessarily looking for VBA o
this, just hoping a few functions will do the trick.

Thanks

 

[Bernie Deitrick]

Trixie,

This seems to work okay - I'm assuming that all your numeric values are in
column P....

=SUMPRODUCT((Sheet1!$K$8:$O$26=R119)*(Sheet1!$P$8:$P$26))

HTH,
Bernie
MS Excel MVP

 

[T. Valko]

Try this:

=SUMPRODUCT((K8:O26=Sheet2!R119)*P826)

 

[Trixie]

SWEEEEET!!!

Thanks Bernie, this gave me exactly what I was looking for.

I am now off to find out everything I can about the 'SUMPRODUCT
function so I know exactly what it is doing!

It amazes me how you guys can figure this out in such a short tim
while I have struggled for DAYS with it. Guess that's why you're th
MVP & get paid the big bucks Blink1

This place is the BEST...luv

Thanks~

 

[Jacob Skaria]

Trixie

Bob Phillips explains sumproduct() in much more detail here..

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

If this post helps click Yes

 

[Rick Rothstein]

Something seems to be wrong with your link... try it out.

--
Rick (MVP - Excel)

Trixie

Bob Phillips explains sumproduct() in much more detail here..

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

If this post helps click Yes

 

[Jacob Skaria]

Thanks Rick. Not sure thats been moved recently...

http://www.lqnet.com/Excel/sumproduct.aspx

If this post helps click Yes
---------------
Jacob Skaria

Something seems to be wrong with your link... try it out.

--
Rick (MVP - Excel)

Trixie

Bob Phillips explains sumproduct() in much more detail here..

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

If this post helps click Yes

 

[Trixie]

You guys are AWESOME!!

Based on some research I did, including the link Jacob provided, I ca
understand what the formula Bernie posted does. I've learned a ne
function within Excel aha!

I was further able to define the table ranges with names; again thank
to the folks here at the site.

I tried to click the 'Thanks' button in a couple of places in thi
thread, but receive an error:
Warning: array_merge() [function.array-merge]: Argument #2 is not a
array in [path]/post_thanks.php on line 57

Please consider yourselves THANKED with much appreciation