|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 10:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com