Now that I look at it again, I can't see why either.<g

Gaurav... if you are still following this thread, replace the formula I
originally posted with this one...

=INDEX(Sheet1!$A$2:$C12,MATCH(REPLACE($A2,2,0,"* "),
Sheet1!$A$2:$A12,0),COLUMN(Sheet1!B2))

Rick


"T. Valko" wrote in message
...
Can't see why you're using these:

ROW(Sheet1!$A$2)+
-ROW(Sheet1!$A$2)
PROPER(...)


--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
Assuming your main data is on Sheet1 (change the reference as required)
starting in Row 2 (Row 1 is assumed to be a header) and assuming the
worksheet where your IDs are has the ID Name in Column A starting in Row
2 also, then put this formula in Column B and copy across, then copy
those all down.

=INDEX(Sheet1!$A$2:$C12,ROW(Sheet1!$A$2)+MATCH(PRO PER(REPLACE($A2,2,0,"*
")),Sheet1!$A$2:$A12,0)-ROW(Sheet1!$A$2),COLUMN(Sheet1!B2))

Rick


"Gaurav" wrote in message
...
Hi All,

In one sheet I have names. for example - Michael Jackson and then some
information in the rest of the columns. In the other sheet I have the
IDs which is first initial and the last name without space. Example -
MJACKSON. I need to use VLOOKUP but i need to find this ID in the names
and then return the values from rest of the columns.

Thanks for any help.