lookup with some specific characters

  • Thread starter Thread starter Gaurav
  • Start date Start date
G

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.
 
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
 
Damn! The newsreader-breaks-at-spaces got me again. There is a single blank
space following that asterisk in the formula I posted.

Rick
 
I got that one :)

Thanks a ton.



Rick Rothstein (MVP - VB) said:
Damn! The newsreader-breaks-at-spaces got me again. There is a single
blank space following that asterisk in the formula I posted.

Rick
Click to expand...
 
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
 
Thanks Rick.


Rick Rothstein (MVP - VB) said:
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
Click to expand...
 
Back
Top