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