Try this:

=SUMPRODUCT((K8:O26=Sheet2!R119)*P8:P26)

--
Biff
Microsoft Excel MVP


"Trixie" wrote in message
...

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

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

*Sheet1*: I have a table that is 6x19 - $K$8:$P$26. There could be
values in any of the fields, and their location within the table could
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, although
there will be a number of them throughout Sheet2) find a match in Sheet1
$K$8:$O$26 ($L$9) of the and return a corresponding value from Sheet1
$P$8:$P$26 ($P$9 - 876) that is on the same row as the Sheet2 R119
value.

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

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

Thanks~


--
Trixie

~TRIXIE
------------------------------------------------------------------------
Trixie's Profile: http://www.thecodecage.com/forumz/member.php?userid=438
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=117444