Hello!
I'm trying to look up a column of keywords looking for any term containing specific word e.g. 'suit' and display that keyword in another worksheet with the values for the keyword in the same row next to it.
I've tried vlookups, match, index etc and they didn't work. I've got a feeling I need to add a couple of formulas together but I'm not sure how to do that.
Thanks,
Nick
HI,
in the example in G1 the substring you're asking to search.
=IF(ISERROR(INDEX(B$2:B$20,SMALL(IF(ISNUMBER(SEARCH($G$1,A$2:A$20)),ROW($A$2:$A$20)-1),ROWS($A$1:$A1)))),"",INDEX(B$2:B$20,SMALL(IF(ISNUMBER(SEARCH($G$1,A$2:A$20)),ROW($A$2:$A$20)-1),ROWS($A$1:$A1))))
or from Excel 2007
=IFERROR(INDEX(B$2:B$20,SMALL(IF(ISNUMBER(SEARCH($G$1,A$2:A$20)),ROW($A$2:$A$20)-1),ROWS($A$1:$A1))),"")
Both formulae are array to be confirmed with control+shift+enter.
See the attempt attached.
Regards
Last edited by canapone; 10-28-2011 at 09:17 AM. Reason: Missed a ";" from Ita settings in traducing the formula
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks