|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 15th, 2009, 06:16 PM
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
April 15th, 2009, 06:37 PM
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
April 15th, 2009, 09:38 PM
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
April 15th, 2009, 09:46 PM
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
April 15th, 2009, 10:03 PM
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
April 15th, 2009, 10:34 PM
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
April 16th, 2009, 06:22 PM
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
April 16th, 2009, 06:36 PM
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
April 17th, 2009, 01:56 AM
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
April 17th, 2009, 10:41 PM
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
