• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Text search location

A

AndiTails

New Member
Hi all, first post - be nice! :)


I'm trying to pull some data off of multiple sheets which are automatically generated each Sunday. The problem I have, the sheets are 250-285 rows long, and the columns grow every week (with trend information).


If I need to pull a specific bit of data, the titles in the rows aren't always in the same place.. If a category hasn't been sold, it won't appear in one sheet, but will be in another - pushing them 'out of whack' when compared.


Is there a way in which I could search for a word or phrase in a column, and the output be its row number? I can then use that in a HLOOKUP.

I can't use a VLOOKUP, as sometimes the weeks don't update or 1 is missed if there are no sales in that category - so I need to lookup both the week no., and the category I want to find.


So:


x 1 2 3 4 5 6 7

a

b

c

d


I would want to look up 5c, but the location of 5c isn't necessarily in the same place, row b might not be there, and 4 might be missing from the sequence..


I hope that makes some sense!!


Or - another possibility - it is possible for a VLOOKUP to return the last piece of data? So I could VLOOKUP the phrase I want, and it would return the farest right column, of farest -1 if I want a WoW comparison, etc.
 
I'm not sure I know how to solve this particular problem for you or not, but something I've been using to search a cell for specific text is:


=ISNUMBER(search(find_text,within_text,[start_num])


In this case I wrote this specific formula:


=ISNUMBER(SEARCH("blog",B1))


_ A___ | ____B

1 TRUE | Chandoo has a teriffic blog


It produces a true/false condtion, but I could also nest this into an IF statement to provide a numeric output.


=if((ISNUMBER(SEARCH("blog",B1)),1,0)


So if it finds the word "blog", it produces a 1, or a zero if if doesn't.
 
I should add something here that you COULD apply the more basic formula to conditional formatting.


If you have Excel 2007 then there's a feature that does this automatically within Excel.


If you have an earlier version of Excel though, from the left hand drop down, select FORMULA IS, instead of CELL VALUE IS, and enter your formula.


You can use the simpler formula here:


=ISNUMBER(SEARCH("blog",B1))


Note: It's important to remove the dollar signs from your variables if you want the cell references to adjust relative to your format pasting. :)
 
In you third paragraph, you were asking for a way to return the ROW NUMBER to a HLOOKUP formula. I suggest you use MATCH("Data you're lookung for", Range).
 
I had a hankering it might be MATCH, but didn't know what to do with it. (Not used it before!)


Perfect - thanks David! That's enough for me to work on now! Brill.
 
You must log in or register to reply here.
Back
Top