Vlookup

santoshdream

Registered User.
Local time
Today, 12:09
Joined
Jan 22, 2009
Messages
22
Can you help me in using Vlookup;

The senerio is like this :

Sheet 1
Date Loan # Amount
XXX YYYYY AAA
.....n number of data

Sheet 2

Date Loan # Amount descp
XXX YYYY AAA ABC
.... m number of data

I want to paste Desc from sheet2 to sheet1 that matches the details Date, Loan and Amount.
 
Since you want the position of a cell in the range rather than return data you would use Index and Match, Match I think allows you to concatenate search criteria and search ranges which you will need to do, however as you are going to copy and paste then I think that you are going to need to write code , you may be able to construct a formula I suppose, and although you can use worksheet functions in code it may be easier to do it all yourself.
wouldn't know unless I had the time to try and data to try it on.

Brian
 
You cannot search in excel on multiple columns...
You would have to concatinate the multiple columns into one column and VLookup on that one column.
 
Hi, namliam,

what about the use of SumProduct (not to be used with entire columns)?

Ciao,
Holger
 
Hi,

I am still confused :confused:could you please help me to understand with the data.I would really appreciate your effort. Please find the attached.


santosh
 

Attachments

So if you want to search you have to concatinate the values first....
In Sheet 2, insert a column before D and enter the formula to concatinate the columns:
=A2 & B2 & C2

Then in C2 use vLookup on that column concatinating the same fields on sheet1.

I dont really understand the point of this exercise though, seems kindoff pointless?
 
It turned out to be easy, name the ranges on sheet2 as rdesc, rloan,ramount, rdate

then in say row 2 desc of sheet1

-Index(rdesc,Match(A2&B2&C2,rdate&rloan&ramount,0))

will give you the answer.
adjust for cols.

Brian

PS Like Namliam I don't see the point of the exercise, but it reminded me of how Index and Match work. :D
 
Last edited:
Hello,

It is still giving me the " #NAME?"

I would really appreciate if you could place this formula in my excel sheet & upload it again. Thanks a lot.
Please help urgent.
 
?? Index ?? Match?? Why not use VLookup??

@Santos...
I described step by step your progress, what you need to do... why should I do your work for you and not have you understand it??

I will repeat..
You cannot search on multiple columns, therefor you must make one column to search on... Insert a column before your Desc column and concatinate your first 3 into one like so:
=A2 & B2 & C2


Then in your other sheet use a VLookup to find your desired Desc.
=VLookup(A2 & B2 & C2,...., false )

If you dont know how VLookup works, look it up in the help.

Good luck & Post back questions if you have them
 
?? Index ?? Match?? Why not use VLookup??

Because Index / Match allows you to search across multiple ranges as illustrated.

Santos probably needs to read and understand Index, Match, Array formula , and naming ranges, plus Vlookup.

Brian
 

Users who are viewing this thread

Back
Top Bottom