How best to...?
 

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

How best to...?
 

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


"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

How best to...?
 

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

How best to...?
 

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 time
while I have struggled for DAYS with it. Guess that's why you're the
MVP & get paid the big bucks Blink1

This place is the BEST...luv

Thanks~

Bernie Deitrick;422211 Wrote:
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


"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: 'The Code Cage Forums - View Profile: Trixie'
(http://www.thecodecage.com/forumz/member.php?userid=438)
View this thread:
'How best to...? - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=117444)



--
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

How best to...?
 

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


"Trixie" wrote:


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 time
while I have struggled for DAYS with it. Guess that's why you're the
MVP & get paid the big bucks Blink1

This place is the BEST...luv

Thanks~

Bernie Deitrick;422211 Wrote:
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


"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: 'The Code Cage Forums - View Profile: Trixie'
(http://www.thecodecage.com/forumz/member.php?userid=438)
View this thread:
'How best to...? - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=117444)



--
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

How best to...?
 

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

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
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


"Trixie" wrote:


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 time
while I have struggled for DAYS with it. Guess that's why you're the
MVP & get paid the big bucks Blink1

This place is the BEST...luv

Thanks~

Bernie Deitrick;422211 Wrote:
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


"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: 'The Code Cage Forums - View Profile: Trixie'
(http://www.thecodecage.com/forumz/member.php?userid=438)
View this thread:
'How best to...? - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=117444)



--
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

How best to...?
 

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

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

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


"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
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


"Trixie" wrote:


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 time
while I have struggled for DAYS with it. Guess that's why you're the
MVP & get paid the big bucks Blink1

This place is the BEST...luv

Thanks~

Bernie Deitrick;422211 Wrote:
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


"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: 'The Code Cage Forums - View Profile: Trixie'
(http://www.thecodecage.com/forumz/member.php?userid=438)
View this thread:
'How best to...? - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=117444)



--
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

How best to...?
 

You guys are AWESOME!!

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

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

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

Please consider yourselves THANKED with much appreciation.


--
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