Vlookup

  • Thread starter Thread starter Dinesh
  • Start date Start date
D

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
 
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.
 
I am getting a "#value!" error.

Sheeloo said:
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.
Click to expand...
 
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))))
 
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))))
 
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))
 
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.
 
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?
 
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!
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

match and extract 4
match or lookup 3
sumif error 4
Triple IF? 2
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Copy from sheet to sheet when matches criteria 2
How Do I Avoid #REF! In Formulas? 4
SUM Q 2

Back
Top