| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I use specific characters to hold value | Excel Worksheet Functions | |||
| Replacing specific characters | Excel Discussion (Misc queries) | |||
| find cells that contain specific characters | Excel Worksheet Functions | |||
| Replacing specific characters with spaces | Excel Worksheet Functions | |||
| Pulling out specific characters | Excel Discussion (Misc queries) | |||
![Gaurav[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
