VLOOKUP? Locate specified text in column, return result X
Posted by Kevin on January 23, 2002 2:08 PM
I want a lookup in column U, to look in column Q through a string of text such as (RETAIL,CG,6 TO 10 STORES, ONLINE SALES SUITE-YES, GROSS ANNUAL SALES - $5 TO $10 MILLION, FC,GA,WIRELESS COMM, BJ)
and if the text "gross annual sales - $5 - $10 million exist in that row, return the value "X" in column U, but I am having no look in getting the lookup to work. Any suggestions guys and gals??
Posted by Dave S on January 23, 2002 2:27 PM
Vlookup is not the function you want. Vlookup will look for the string of text in a table and return data from an adjacent row in the same table. It does not return any other values. For that you either need to write some VBA or, depending on the amount of data you have, you could put an if statement in each cell of the form:
if(a1="text", u1="X","")
This will put an x in u1 for any string that matches the "text" and nothing ("") in every other row. You'd have to scroll this down through every row of data though, do if there's too much it's not that practical.
Dave
Posted by Aladin Akyurek on January 23, 2002 2:32 PM
=IF(COUNTIF(Q:Q,"*search-string*"),"X","No Match")
where *search-string* is the string (between stars) you're looking for.
===========
Posted by kevin on January 23, 2002 2:35 PM
I may have taken the long way around it, but I did end up using the =SEARCH method to locate cells in 1800 rows of data that matched the text I was looking for, then did a find/replace all - and replaced the character count returned by the SEARCH with the "X". Thanks for the information though, it really gives me other options moving forward. As always, you guys (and gals) are the best of the best.
=IF(COUNTIF(Q:Q,"*search-string*"),"X","No Match") : and if the text "gross annual sales - $5 - $10 million exist in that row, return the value "X" in column U, but I am having no look in getting the lookup to work. Any suggestions guys and gals??
