How to MATCH or VLOOKUP & copy using 2 excel files

[Sandeep]

In excel2007, I'm having 2 files. In file#1, in Sheet1, Column A contains
some names. In file#2, in Sheet1, Column D contains same (Case Matching)
names but in DIFFERENT ORDERS & Column E contains some numerical values
against each names.
In file#1, in Sheet1, in Column H, How to get the same numerical values
(Column E of file#2) against the names in Column A?
Note: File#2, Column D may contain ADDITIONAL names also(for which there is
some value in Column E).

Please help without VBA i.e. thr' excel functions
Sandeep

 

[muddan madhu]

try this

=INDEX(Sheet2!$E$2:$E$10,MATCH("*"&Sheet1!A2&"*",Sheet2!$D$2:$D$10,0))

 

[Sandeep]

Thanks Madhu for replying so fast. Let me put total thing in prospective:

There are 2 excel files: A.xls & B.xls
In file B.xls in Sheet1, there are 5 columns F,G,H,I &J
F, I, J = contains Name
G & H = contains numerical values

Now, In file A.xls, in Sheet1, I write names in Column A.
I want: If the name written by me in Column A (in A.xls) matches exactly
(Case Sensitive) as Column F (in B.xls), it should produce results as follows:
Column B (in A.xls)=Column G (of B.xls)
Column C (in A.xls)=Column H (of B.xls)
Column D (in A.xls)=Column I (of B.xls)
Column E (in A.xls)=Column J (of B.xls)

Please reply.
Sandeep

 

[muddan madhu]

Hi dude,

I didn't check your reply, sorry for that

in worksheet A.xls - Cell A2 you will type the name ( assumed that
data is in sheet 1 of the file mentioned above by you )

in cell B2 put this formula =INDEX([B.xls]Sheet1!$G$2:$G$20,MATCH($A2,
[B.xls]Sheet1!$F$2:$F$20,0))

in cell C2 put this formula =INDEX([B.xls]Sheet1!$H$2:$H$20,MATCH($A2,
[B.xls]Sheet1!$F$2:$F$20,0))

in cell D2 put this formula =INDEX([B.xls]Sheet1!$I$2:$I$20,MATCH($A2,
[B.xls]Sheet1!$F$2:$F$20,0))

in cell E2 put this formula =INDEX([B.xls]Sheet1!$J$2:$J$20,MATCH($A2,
[B.xls]Sheet1!$F$2:$F$20,0))




If you find any difficulties or u need more assistance then you can
also send your query to me - (e-mail address removed)