Vlookup

[Dinesh]

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

 

[Sheeloo]

Try
=IF(ISNA(Vlookup(A1,Sheet1!A:C,3,False),"",ISNA(Vlookup(A1,Sheet1!A:C,3,False))
&
IF(ISNA(Vlookup(A1,Sheet1!B:C,2,False),"",ISNA(Vlookup(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]

I am getting a "#value!" error.

Try
=IF(ISNA(Vlookup(A1,Sheet1!A:C,3,False),"",ISNA(Vlookup(A1,Sheet1!A:C,3,False))
&
IF(ISNA(Vlookup(A1,Sheet1!B:C,2,False),"",ISNA(Vlookup(A1,Sheet1!B:C,2,False))

assuming DM# and CM# don't have any number in common.
and COL A has DM# or CM.

 

[Sheeloo]

Sorry, forgot to close ISNA... try
=IF(ISNA(VLOOKUP(A1,Sheet1!A:C,3,FALSE)),"",ISNA(VLOOKUP(A1,Sheet1!A:C,3,FALSE))&IF(ISNA(VLOOKUP(A1,Sheet1!B:C,2,FALSE)),"",ISNA(VLOOKUP(A1,Sheet1!B:C,2,FALSE))))

 

[Dinesh]

Sorry, but still getting the same error message. Below is my formula.

=IF(ISNA(VLOOKUP(A104,Loans!$C$21:$H$86,6,0)),"",ISNA(VLOOKUP(A104,Loans!$C$21:$H$86,6,0))&IF(ISNA(VLOOKUP(A104,Loans!$D$21:$H$86,5,0)),"",ISNA(VLOOKUP(A104,Loans!$D$21:$H$86,5,0))))

 

[Sheeloo]

ISNA is required only once in each set
try
=IF(ISNA(VLOOKUP(A104,Loans!$C$21:$H$86,6,0)),"Test1",VLOOKUP(A104,Loans!$C$21:$H$86,6,0))&IF(ISNA(VLOOKUP(A104,Loans!$D$21:$H$86,5,0)),"
Test2",VLOOKUP(A104,Loans!$D$21:$H$86,5,0))

 

[Dinesh]

HI,

Still getting the same error message.
See below.

=IF(ISNA(VLOOKUP(A104,Loans!$C$21:$H$86,6,0)),"",VLOOKUP(A104,Loans!$C$21:$H$86,6,0))&IF(ISNA(VLOOKUP(A104,Loans!$D$21:$H$86,5,0)),"",VLOOKUP(A104,Loans!$D$21:$H$86,5,0))

Thanks.

 

[Sheeloo]

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]

see attached

http://wikisend.com/download/463882/Book1.xlsx

 

[Sheeloo]

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!