match,lookup or vlookup????

Hi guys, I 'm having a bit difficulty finding out the right solution of my
poblem.
Here is the case.

FIRST LIST SECOND LIST
Acct Amt Acct
Amt
4001400 $679 4000086 $1000
4000334 $23456 4000087 $213
4000480 $2776 4001400 $1234
4000764 $565 4000791 $2035
4000772 $454 400125 $2379
4000791 $656 401253 $120

Now what I like to do is (by using some kind a combination of vlookup, lookup,
or match commands) that use the amount of the second list with there
corresponding acct # but if there is any acct match with the first list use
the first list amount. So for instance, acct # 4001400 should display $679
and NOT $1234 and also for acct #4000791 should have the result $656 and not
$2035. The rest accts should have the amount from the second list.
I hope I able to explain it properly.
Your help will be greatly appreciated.
Thanks
Morphyus


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200507/1

One way:

=IF(ISERROR(VLOOKUP(D9,A1:B6,2,FALSE)), VLOOKUP(D9,
C1:D6,2,FALSE),VLOOKUP(D9, A1:B6,2,FALSE))

This assumes the first list is in the range a1:b6, and the second list is in
the range c1:d6, and the account number you are looking up is in cell d9.


"Morphyus C via OfficeKB.com" <forum@OfficeKB.com> wrote in message
news:51433A11452ED@OfficeKB.com...
> Hi guys, I 'm having a bit difficulty finding out the right solution of my
> poblem.
> Here is the case.
>
> FIRST LIST SECOND LIST
> Acct Amt Acct
> Amt
> 4001400 $679 4000086 $1000
> 4000334 $23456 4000087 $213
> 4000480 $2776 4001400 $1234
> 4000764 $565 4000791
> $2035
> 4000772 $454 400125 $2379
> 4000791 $656 401253 $120
>
> Now what I like to do is (by using some kind a combination of vlookup,
> lookup,
> or match commands) that use the amount of the second list with there
> corresponding acct # but if there is any acct match with the first list
> use
> the first list amount. So for instance, acct # 4001400 should display $679
> and NOT $1234 and also for acct #4000791 should have the result $656 and
> not
> $2035. The rest accts should have the amount from the second list.
> I hope I able to explain it properly.
> Your help will be greatly appreciated.
> Thanks
> Morphyus
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...excel/200507/1

Thank you L. Clark...actually I should have explain it more. After these two
list I have the third list with just the Acct # but it should populate the $
amt across it as I have mentioned in my posting.

Lewis Clark wrote:
>One way:
>
>=IF(ISERROR(VLOOKUP(D9,A1:B6,2,FALSE)), VLOOKUP(D9,
>C1:D6,2,FALSE),VLOOKUP(D9, A1:B6,2,FALSE))
>
>This assumes the first list is in the range a1:b6, and the second list is in
>the range c1:d6, and the account number you are looking up is in cell d9.
>
>> Hi guys, I 'm having a bit difficulty finding out the right solution of my
>> poblem.
>[quoted text clipped - 24 lines]
>> Thanks
>> Morphyus


--
Message posted via http://www.officekb.com

Assumptions:

A2:B7 contains your first table

D2:E7 contains your second table

Column G, starting at G2, contains your list of account numbers

Formula:

H2, copied down:

=VLOOKUP(G2,IF(COUNTIF($A$2:$A$7,G2),$A$2:$B$7,$D$2:$E$7),2,0)

Hope this helps!

In article <5144BB5ED0526@OfficeKB.com>,
"Morphyus C via OfficeKB.com" <forum@OfficeKB.com> wrote:

> Thank you L. Clark...actually I should have explain it more. After these two
> list I have the third list with just the Acct # but it should populate the $
> amt across it as I have mentioned in my posting.
>
> Lewis Clark wrote:
> >One way:
> >
> >=IF(ISERROR(VLOOKUP(D9,A1:B6,2,FALSE)), VLOOKUP(D9,
> >C1:D6,2,FALSE),VLOOKUP(D9, A1:B6,2,FALSE))
> >
> >This assumes the first list is in the range a1:b6, and the second list is in
> >the range c1:d6, and the account number you are looking up is in cell d9.
> >
> >> Hi guys, I 'm having a bit difficulty finding out the right solution of my
> >> poblem.
> >[quoted text clipped - 24 lines]
> >> Thanks
> >> Morphyus

sorry mr. Lewis...I apologize, it wrk like a charm...I thankyou so much
have a blessed day.

Morphyus C wrote:
>Thank you L. Clark...actually I should have explain it more. After these two
>list I have the third list with just the Acct # but it should populate the $
>amt across it as I have mentioned in my posting.
>
>>One way:
>>
>[quoted text clipped - 9 lines]
>>> Thanks
>>> Morphyus


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200507/1

You are welcome.

"Morphyus C via OfficeKB.com" <forum@OfficeKB.com> wrote in message
news:5144EEF5B9418@OfficeKB.com...
> sorry mr. Lewis...I apologize, it wrk like a charm...I thankyou so much
> have a blessed day.
>