Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup
Hi,
I have three columns in Sheet1. COL A has DM#, COL B has CM# and COL C has an amount. Sheet2 has two columns. COL A has DM# and CM#. In COL B, I like to bring the amount based on CM# or DM# from sheet1. Is there a simple formula for this? Thanks, Dinesh |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup
Try
=IF(ISNA(Vlookup(A1,Sheet1!A:C,3,False),"",ISNA(Vl ookup(A1,Sheet1!A:C,3,False)) & IF(ISNA(Vlookup(A1,Sheet1!B:C,2,False),"",ISNA(Vlo okup(A1,Sheet1!B:C,2,False)) assuming DM# and CM# don't have any number in common. and COL A has DM# or CM. "Dinesh" wrote: Hi, I have three columns in Sheet1. COL A has DM#, COL B has CM# and COL C has an amount. Sheet2 has two columns. COL A has DM# and CM#. In COL B, I like to bring the amount based on CM# or DM# from sheet1. Is there a simple formula for this? Thanks, Dinesh |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup
I am getting a "#value!" error.
"Sheeloo" wrote: Try =IF(ISNA(Vlookup(A1,Sheet1!A:C,3,False),"",ISNA(Vl ookup(A1,Sheet1!A:C,3,False)) & IF(ISNA(Vlookup(A1,Sheet1!B:C,2,False),"",ISNA(Vlo okup(A1,Sheet1!B:C,2,False)) assuming DM# and CM# don't have any number in common. and COL A has DM# or CM. "Dinesh" wrote: Hi, I have three columns in Sheet1. COL A has DM#, COL B has CM# and COL C has an amount. Sheet2 has two columns. COL A has DM# and CM#. In COL B, I like to bring the amount based on CM# or DM# from sheet1. Is there a simple formula for this? Thanks, Dinesh |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup
Sorry, forgot to close ISNA... try
=IF(ISNA(VLOOKUP(A1,Sheet1!A:C,3,FALSE)),"",ISNA(V LOOKUP(A1,Sheet1!A:C,3,FALSE))&IF(ISNA(VLOOKUP(A1, Sheet1!B:C,2,FALSE)),"",ISNA(VLOOKUP(A1,Sheet1!B:C ,2,FALSE)))) "Dinesh" wrote: I am getting a "#value!" error. "Sheeloo" wrote: Try =IF(ISNA(Vlookup(A1,Sheet1!A:C,3,False),"",ISNA(Vl ookup(A1,Sheet1!A:C,3,False)) & IF(ISNA(Vlookup(A1,Sheet1!B:C,2,False),"",ISNA(Vlo okup(A1,Sheet1!B:C,2,False)) assuming DM# and CM# don't have any number in common. and COL A has DM# or CM. "Dinesh" wrote: Hi, I have three columns in Sheet1. COL A has DM#, COL B has CM# and COL C has an amount. Sheet2 has two columns. COL A has DM# and CM#. In COL B, I like to bring the amount based on CM# or DM# from sheet1. Is there a simple formula for this? Thanks, Dinesh |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup
Sorry, but still getting the same error message. Below is my formula.
=IF(ISNA(VLOOKUP(A104,Loans!$C$21:$H$86,6,0)),"",I SNA(VLOOKUP(A104,Loans!$C$21:$H$86,6,0))&IF(ISNA(V LOOKUP(A104,Loans!$D$21:$H$86,5,0)),"",ISNA(VLOOKU P(A104,Loans!$D$21:$H$86,5,0)))) "Sheeloo" wrote: Sorry, forgot to close ISNA... try =IF(ISNA(VLOOKUP(A1,Sheet1!A:C,3,FALSE)),"",ISNA(V LOOKUP(A1,Sheet1!A:C,3,FALSE))&IF(ISNA(VLOOKUP(A1, Sheet1!B:C,2,FALSE)),"",ISNA(VLOOKUP(A1,Sheet1!B:C ,2,FALSE)))) "Dinesh" wrote: I am getting a "#value!" error. "Sheeloo" wrote: Try =IF(ISNA(Vlookup(A1,Sheet1!A:C,3,False),"",ISNA(Vl ookup(A1,Sheet1!A:C,3,False)) & IF(ISNA(Vlookup(A1,Sheet1!B:C,2,False),"",ISNA(Vlo okup(A1,Sheet1!B:C,2,False)) assuming DM# and CM# don't have any number in common. and COL A has DM# or CM. "Dinesh" wrote: Hi, I have three columns in Sheet1. COL A has DM#, COL B has CM# and COL C has an amount. Sheet2 has two columns. COL A has DM# and CM#. In COL B, I like to bring the amount based on CM# or DM# from sheet1. Is there a simple formula for this? Thanks, Dinesh |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup
ISNA is required only once in each set
try =IF(ISNA(VLOOKUP(A104,Loans!$C$21:$H$86,6,0)),"Tes t1",VLOOKUP(A104,Loans!$C$21:$H$86,6,0))&IF(ISNA(V LOOKUP(A104,Loans!$D$21:$H$86,5,0))," Test2",VLOOKUP(A104,Loans!$D$21:$H$86,5,0)) "Dinesh" wrote: Sorry, but still getting the same error message. Below is my formula. =IF(ISNA(VLOOKUP(A104,Loans!$C$21:$H$86,6,0)),"",I SNA(VLOOKUP(A104,Loans!$C$21:$H$86,6,0))&IF(ISNA(V LOOKUP(A104,Loans!$D$21:$H$86,5,0)),"",ISNA(VLOOKU P(A104,Loans!$D$21:$H$86,5,0)))) "Sheeloo" wrote: Sorry, forgot to close ISNA... try =IF(ISNA(VLOOKUP(A1,Sheet1!A:C,3,FALSE)),"",ISNA(V LOOKUP(A1,Sheet1!A:C,3,FALSE))&IF(ISNA(VLOOKUP(A1, Sheet1!B:C,2,FALSE)),"",ISNA(VLOOKUP(A1,Sheet1!B:C ,2,FALSE)))) "Dinesh" wrote: I am getting a "#value!" error. "Sheeloo" wrote: Try =IF(ISNA(Vlookup(A1,Sheet1!A:C,3,False),"",ISNA(Vl ookup(A1,Sheet1!A:C,3,False)) & IF(ISNA(Vlookup(A1,Sheet1!B:C,2,False),"",ISNA(Vlo okup(A1,Sheet1!B:C,2,False)) assuming DM# and CM# don't have any number in common. and COL A has DM# or CM. "Dinesh" wrote: Hi, I have three columns in Sheet1. COL A has DM#, COL B has CM# and COL C has an amount. Sheet2 has two columns. COL A has DM# and CM#. In COL B, I like to bring the amount based on CM# or DM# from sheet1. Is there a simple formula for this? Thanks, Dinesh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |