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

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