| 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
HI,
Still getting the same error message. See below. =IF(ISNA(VLOOKUP(A104,Loans!$C$21:$H$86,6,0)),"",V LOOKUP(A104,Loans!$C$21:$H$86,6,0))&IF(ISNA(VLOOKU P(A104,Loans!$D$21:$H$86,5,0)),"",VLOOKUP(A104,Loa ns!$D$21:$H$86,5,0)) Thanks. "Sheeloo" wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
I copied and pasted your formula and it worked for me...
download http://wikisend.com/download/601650/Sample.xls and see... If it still does not work then you may send your file (after removing any privay related information) to me or upload it to wikisend and paste the link here... What is the value in A104? "Dinesh" wrote: HI, Still getting the same error message. See below. =IF(ISNA(VLOOKUP(A104,Loans!$C$21:$H$86,6,0)),"",V LOOKUP(A104,Loans!$C$21:$H$86,6,0))&IF(ISNA(VLOOKU P(A104,Loans!$D$21:$H$86,5,0)),"",VLOOKUP(A104,Loa ns!$D$21:$H$86,5,0)) Thanks. "Sheeloo" wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
see attached
http://wikisend.com/download/463882/Book1.xlsx "Sheeloo" wrote: I copied and pasted your formula and it worked for me... download http://wikisend.com/download/601650/Sample.xls and see... If it still does not work then you may send your file (after removing any privay related information) to me or upload it to wikisend and paste the link here... What is the value in A104? "Dinesh" wrote: HI, Still getting the same error message. See below. =IF(ISNA(VLOOKUP(A104,Loans!$C$21:$H$86,6,0)),"",V LOOKUP(A104,Loans!$C$21:$H$86,6,0))&IF(ISNA(VLOOKU P(A104,Loans!$D$21:$H$86,5,0)),"",VLOOKUP(A104,Loa ns!$D$21:$H$86,5,0)) Thanks. "Sheeloo" wrote: 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
I copied your data to a new file and the formula worked...without any change.
Same thing is not working in your file... When I entered =LEN(A104) I got #VALUE error!!! I could not understand why! "Dinesh" wrote: see attached http://wikisend.com/download/463882/Book1.xlsx "Sheeloo" wrote: I copied and pasted your formula and it worked for me... download http://wikisend.com/download/601650/Sample.xls and see... If it still does not work then you may send your file (after removing any privay related information) to me or upload it to wikisend and paste the link here... What is the value in A104? "Dinesh" wrote: HI, Still getting the same error message. See below. =IF(ISNA(VLOOKUP(A104,Loans!$C$21:$H$86,6,0)),"",V LOOKUP(A104,Loans!$C$21:$H$86,6,0))&IF(ISNA(VLOOKU P(A104,Loans!$D$21:$H$86,5,0)),"",VLOOKUP(A104,Loa ns!$D$21:$H$86,5,0)) Thanks. "Sheeloo" wrote: 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 | |||
Linear Mode
