Vlookup

[santoshdream]

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.

 

[Brianwarnock]

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

 

[namliam]

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.

 

[HaHoBe]

Hi, namliam,

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

Ciao,
Holger

 

[santoshdream]

Hi,

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


santosh

 

[namliam]

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?

 

[Brianwarnock]

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.

 

[santoshdream]

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.

 

[namliam]

?? 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

 

[Brianwarnock]

?? 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