lookup with some specific characters

[Gaurav]

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.

 

[Rick Rothstein \(MVP - VB\)]

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(PROPER(REPLACE($A2,2,0,"*
")),Sheet1!$A$2:$A12,0)-ROW(Sheet1!$A$2),COLUMN(Sheet1!B2))

Rick

 

[Rick Rothstein \(MVP - VB\)]

Damn! The newsreader-breaks-at-spaces got me again. There is a single blank
space following that asterisk in the formula I posted.

Rick

 

[Gaurav]

I got that one

Thanks a ton.

Damn! The newsreader-breaks-at-spaces got me again. There is a single
blank space following that asterisk in the formula I posted.

Rick

 

[T. Valko]

Can't see why you're using these:

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Gaurav]

Thanks Rick.

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