Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup single value in one sheet, return multiple results from theother sheet
hi guys,
i have two sheets, one to display results (Reults tab) & the other tab containing the data (Data tab) what i am trying to do is some how create a search function and have a forumula which contains a LIKE function that looks up the data table RANGE = Data!A2:K255 the search needs to lookup the primary column Data!B2:B255 ... if any results are found .. show them on the results tab.. and if multiple results are found, display those as well.. (in either instance, the whole row of information in respect to the results need to be dislayed and hopefully no duplicates are found .. eg, Data!A:K of a hit) is there a formula that can achieve this? oh, the search is TEXT based and there should be no empty cells within the dataset cheers & thanks for your assistance |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup single value in one sheet, return multiple results fromthe other sheet
after some MASSIVE googling, i have stumbled accross this
B1 = Search box (txt field) A6 (which will be a hidden column) contains =MATCH($B$1,Data! A2:A255,0). this formula provides the first instance of the result and provides the row number A7 contains =MATCH($B$1,OFFSET(Data!$A$1,A6+1,0,8-(A6+1),1),0)+A6. this is supposed to look for the next row number which contains a match and provide that row number and througout my other columns, i have B6=OFFSET(Data!$A$1,A6,1) B7=OFFSET(Data!$A$1,A6,2) B8=OFFSET(Data!$A$1,A6,3) and so on 2 things i cannot recitify.. 1, the match has to be EXACT ... unfortunately i cannot use exact .. needs to be LIKE .. eg, i cant use the search word "boat" as the range of data has "boats" 2, it comes up with multile .. irrelevent results.. is there any resolution to the above cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup one value in list and return multiple results | Excel Worksheet Functions | |||
V lookup with 2 criteria to return results for multiple columns | Excel Worksheet Functions | |||
single lookup value with multiple results | Excel Worksheet Functions | |||
multi sheet lookup with multiple results | Excel Discussion (Misc queries) | |||
Return Multiple Results with Lookup | Excel Worksheet Functions |