Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 , "Morphyus C via 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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
You are welcome. :)
"Morphyus C via OfficeKB.com" wrote in message ... sorry mr. Lewis...I apologize, it wrk like a charm...I thankyou so much have a blessed day. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Vlookup with validation | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions |