Hi,

In the first worksheet I have a set of codes which I need to compare to
another worksheet and find matching codes and return the value
associated with it in the adjacent column. Normally I would use a
vlookup however in this second worksheet I have 4 columns set out as
below:

Code | Number | Code | Number

x123 2 z179 3
y567 1 x123 2

I would therefore need to look in both "code" columns to see if I can
find the code from the first worksheet and take the number from the
"number" column to its right. One additional problem, the code we are
looking for could appear in BOTH columns and in which case I would need
to sum both values. Therefore from the above example I would get:

x123 = 4
z179 = 3
y567 = 1

Is this possible in either a formula or macro? I would refer the
former but please let me know about either.

Many thanks in advance,

GW


--
giantwolf
------------------------------------------------------------------------
giantwolf's Profile: http://www.excelforum.com/member.php...o&userid=24718
View this thread: http://www.excelforum.com/showthread...hreadid=399102

You'd add two SUMIFs:

=SUMIF($A$2:$A$3,$A7,$B$2:$B$3)+SUMIF($C$2:$C$3,$A 7,$D$2:$D$3)

A2:A3 is the left column of codes, B2:B3 is the left column of
numbers.

C2:C3 is the right column of codes, D2:D3 is the right column of
numbers.

A7 is the code you're trying to sum up.

Let me know if you'd like a sample file.


--
Excel_Geek


------------------------------------------------------------------------
Excel_Geek's Profile: http://www.excelforum.com/member.php...o&userid=26423
View this thread: http://www.excelforum.com/showthread...hreadid=399102

Ah, as simple as that! I think I imagined something much more
complicated in my mind. Thanks, no sample file needed, I'm fine with
sumif's, just never occurred to me to add the two together!

Thanks again,

GW



Excel_Geek Wrote:
You'd add two SUMIFs:

=SUMIF($A$2:$A$3,$A7,$B$2:$B$3)+SUMIF($C$2:$C$3,$A 7,$D$2:$D$3)

A2:A3 is the left column of codes, B2:B3 is the left column of
numbers.

C2:C3 is the right column of codes, D2:D3 is the right column of
numbers.

A7 is the code you're trying to sum up.

Let me know if you'd like a sample file.


--
giantwolf
------------------------------------------------------------------------
giantwolf's Profile: http://www.excelforum.com/member.php...o&userid=24718
View this thread: http://www.excelforum.com/showthread...hreadid=399102