| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Data
****al,
I have data like Farmer _ID Name_1 Name_2 Link Path 100003 Kamadinni Shasappa E:\Sujeet\Kn100005.JPG 100005 Khaza Raz E:\Sujeet\Kn100003.JPG 100017 Jagadal Laxman E:\Sujeet\kn100017.JPG i want to match farmer ID with LinkPath with lastno.100005 I need like Farmer _ID Name_1 Name_2 Link Path 100003 Kamadinni Shasappa E:\Sujeet\Kn100003.JPG how to i match tham. thanks Tiya |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Data
Hi
Assuming your data table is on Sheet1 in columns A:D and that you want to pull the data across to Sheet2 starting in A2 On sheet2 cell B2enter =IF(A2="","",VLOOKUP(A2,Sheet1!A:D,COLUMN(),0)) Copy across through C2:D2 Copy B2:D2 down the sheet as far as you require COLUMN() returns the column number, 2 for B, 3 for C etc. so it is stepping up the offset for you automatically, instead of you having to change the formula to 2 then 3 then 4 -- Regards Roger Govier "Tiya" wrote in message ... ****al, I have data like Farmer _ID Name_1 Name_2 Link Path 100003 Kamadinni Shasappa E:\Sujeet\Kn100005.JPG 100005 Khaza Raz E:\Sujeet\Kn100003.JPG 100017 Jagadal Laxman E:\Sujeet\kn100017.JPG i want to match farmer ID with LinkPath with lastno.100005 I need like Farmer _ID Name_1 Name_2 Link Path 100003 Kamadinni Shasappa E:\Sujeet\Kn100003.JPG how to i match tham. thanks Tiya |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Data
Thanks for reply But,
Basicaly i want to match Farmer _ID with linkPath. Where last no. of linkpath is to match with Farmer id. That should come in one row. Like Farmer _ID Name_1 Name_2 Link Path 100003 Kamadinni Shasappa E:\Sujeet\Kn100003.JPG 100017 Jagadal Laxman E:\Sujeet\kn100017.JPG My data is Like Farmer _ID Name_1 Name_2 Link Path 100003 Kamadinni Shasappa E:\Sujeet\Kn100003.JPG 100005 Khaza Raz E:\Sujeet\Kn100017.JPG 100017 Jagadal Laxman E:\Sujeet\kn100024.JPG Pls help. Thanks "Roger Govier" wrote: Hi Assuming your data table is on Sheet1 in columns A:D and that you want to pull the data across to Sheet2 starting in A2 On sheet2 cell B2enter =IF(A2="","",VLOOKUP(A2,Sheet1!A:D,COLUMN(),0)) Copy across through C2:D2 Copy B2:D2 down the sheet as far as you require COLUMN() returns the column number, 2 for B, 3 for C etc. so it is stepping up the offset for you automatically, instead of you having to change the formula to 2 then 3 then 4 -- Regards Roger Govier "Tiya" wrote in message ... ****al, I have data like Farmer _ID Name_1 Name_2 Link Path 100003 Kamadinni Shasappa E:\Sujeet\Kn100005.JPG 100005 Khaza Raz E:\Sujeet\Kn100003.JPG 100017 Jagadal Laxman E:\Sujeet\kn100017.JPG i want to match farmer ID with LinkPath with lastno.100005 I need like Farmer _ID Name_1 Name_2 Link Path 100003 Kamadinni Shasappa E:\Sujeet\Kn100003.JPG how to i match tham. thanks Tiya |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Data
Hi
then if all you want is the linkpath value, use =IF(A2="","",VLOOKUP(A2,Sheet1!A:D,4,0)) -- Regards Roger Govier "Tiya" wrote in message ... Thanks for reply But, Basicaly i want to match Farmer _ID with linkPath. Where last no. of linkpath is to match with Farmer id. That should come in one row. Like Farmer _ID Name_1 Name_2 Link Path 100003 Kamadinni Shasappa E:\Sujeet\Kn100003.JPG 100017 Jagadal Laxman E:\Sujeet\kn100017.JPG My data is Like Farmer _ID Name_1 Name_2 Link Path 100003 Kamadinni Shasappa E:\Sujeet\Kn100003.JPG 100005 Khaza Raz E:\Sujeet\Kn100017.JPG 100017 Jagadal Laxman E:\Sujeet\kn100024.JPG Pls help. Thanks "Roger Govier" wrote: Hi Assuming your data table is on Sheet1 in columns A:D and that you want to pull the data across to Sheet2 starting in A2 On sheet2 cell B2enter =IF(A2="","",VLOOKUP(A2,Sheet1!A:D,COLUMN(),0)) Copy across through C2:D2 Copy B2:D2 down the sheet as far as you require COLUMN() returns the column number, 2 for B, 3 for C etc. so it is stepping up the offset for you automatically, instead of you having to change the formula to 2 then 3 then 4 -- Regards Roger Govier "Tiya" wrote in message ... ****al, I have data like Farmer _ID Name_1 Name_2 Link Path 100003 Kamadinni Shasappa E:\Sujeet\Kn100005.JPG 100005 Khaza Raz E:\Sujeet\Kn100003.JPG 100017 Jagadal Laxman E:\Sujeet\kn100017.JPG i want to match farmer ID with LinkPath with lastno.100005 I need like Farmer _ID Name_1 Name_2 Link Path 100003 Kamadinni Shasappa E:\Sujeet\Kn100003.JPG how to i match tham. thanks Tiya |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Data
Some thoughts ..
Assuming source data as posted in cols A to D, data from row2 down, viz, A1:D1 contains the headers: Farmer _ID, Name_1, Name_2, Link Path Put in E2, array-enter the formula by pressing CTRL+SHIFT+ENTER: =IF($A2="","",IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH($ A2,$D$2:$D$100)),0)),"",(INDEX(C$2:C$100,MATCH(TRU E,ISNUMBER(SEARCH($A2,$D$2:$D$100)),0))))) Copy E2 to F2, fill down as far as required. Cols E and F will return the required results from cols C and D ie Name_2, Link Path where the Link Path's embedded number matches the Farmer _ID. It'll return blanks for any non-matches. Adapt the ranges to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tiya" wrote Basicaly i want to match Farmer _ID with linkPath. Where last no. of linkpath is to match with Farmer id. That should come in one row. Like Farmer _ID Name_1 Name_2 Link Path 100003 Kamadinni Shasappa E:\Sujeet\Kn100003.JPG 100017 Jagadal Laxman E:\Sujeet\kn100017.JPG My data is Like Farmer _ID Name_1 Name_2 Link Path 100003 Kamadinni Shasappa E:\Sujeet\Kn100003.JPG 100005 Khaza Raz E:\Sujeet\Kn100017.JPG 100017 Jagadal Laxman E:\Sujeet\kn100024.JPG |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match Data
Errata - there was an unnecessary pair of parens in the earlier array
formula Put in E2, array-enter the formula by pressing CTRL+SHIFT+ENTER: =IF($A2="","",IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH($ A2,$D$2:$D$100)),0)),"",INDEX(C$2:C$100,MATCH(TRUE ,ISNUMBER(SEARCH($A2,$D$2:$D$100)),0)))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| match data to another sheet and return all the data on that row | Excel Worksheet Functions | |||
| Match data in 2 columns and return data from 3rd column | Excel Worksheet Functions | |||
| Find, Match data and paste data between two workbooks | Excel Discussion (Misc queries) | |||
| index,match,match on un-sorted data | Excel Worksheet Functions | |||
| The match and lookup functions can find literal data but not the same data referenced from a cell | Excel Discussion (Misc queries) | |||
Hybrid Mode
