lookup with some specific characters
 

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.

lookup with some specific characters
 

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.

lookup with some specific characters
 

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

Rick


"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.

lookup with some specific characters
 

I got that one :)

Thanks a ton.



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

Rick


"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.

lookup with some specific characters
 

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.

lookup with some specific characters
 

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.

lookup with some specific characters
 

Thanks Rick.


"Rick Rothstein (MVP - VB)" wrote in
message ...
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.