I have a column with values that contain specific keywords I want to check for and create some "IF" statements based on whether it can find a match.
For example:
Column B contains the text string: Payment to Wal-Mart
and I would search for "Wal-Mart" and return the text "Grocery" in column C.
How?
Can you provide a small sample of the data, along with the actual expected results?
In sheet 1:
A1 contains text string: "Online purhcase made from Wal-Mart"
A2 contains text string: "Online purchase made from Sunoco"
A3 contains text string: "Vendor purchase from Mc Donalds on 9/14/08"
etc, etc
In sheet 2:
In a separate list of every merchant that could possibly show up in this list in column A and column B has a category that I custom defined. For example:
A1 would be "Wal-Mart" and B1 would be "Grocery"
A2 would be "Sunoco" and B2 would be "Gas Station"
A3 would be "Mc Donalds" and B3 would be "Fast Food"
etc
Now, going back to sheet 1 in column B I would have a formula that would use something like a VLOOKUP to check for the vendor merchants from sheet 2 to see if they exist in the text strings in sheet 1, and, if they do match it returns the value from of "Category" defined in column B from Sheet 2.
Follow that?
Basically looking for an advanced use of VLOOKUP statement that uses a "Match within text string" approach... does that exist without using VB? Wasn't clear on all the formulas in excel.
thanks
use wild card
=VLOOKUP("*Wal-Mart*",A1:B100,2,FALSE)
or
=VLOOKUP("*"&D1&"*",A1:B100,2,FALSE) where d1 contains string (warning if d1 empty it gives funny results as it will be looking for blanks or spaces depending on how cell was cleared)
Last edited by martindwilson; 11-10-2008 at 04:40 AM.
I think it all depends on whether you would have multiple instances of stores within your string in A ... eg "xyz Walmart Sunoco", if not you could use a SUMPRODUCT with INDEX on Sheet1 in Column B. Using your sample data on Sheet1!A1:A3 and assuming you could have say 75 stores listed on Sheet2 (which you may add to) -- we'll assume a lookup range of 1:100 on Sheet2
Sheet1!B1:
=INDEX(Sheet2!$B$1:$B$100,SUMPRODUCT(--(LEN(Sheet2!$A$1:$A$100)>0),--(ISNUMBER(SEARCH(Sheet2!$A$1:$A$100,$A1))),ROW($1:$100)),0)
However if you have duplicates as per the Walmart Sunoco string the above would produce misleading results... in which case you use a different array (this one requiring SHIFT + CTRL + ENTER to commit)
=INDEX(Sheet2!$B$1:$B$100,MIN(IF(ISNUMBER(SEARCH(Sheet2!$A$1:$A$100,$A1)),ROW($1:$100),1000)),1)
This will return the store type for the first store in Sheet2!A1:A100 that is listed in your Sheet1 A value.
This still isn't 100% watertight pending your strings in Sheet1 but will hopefully point you in the right direction.
Both of the above are based on the fact you don't want to specify which store you're looking for on Sheet1 -- ie the store criteria value needs to be dynamic and not hard wired into the formula itself.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Try...
B1, copied down:
=LOOKUP(9.99999999999999E+307,FIND(" "&Sheet2!$A$1:$A$3&" "," "&A1&" "),Sheet2!$B$1:$B$3)
Note that FIND is case-sensitive. If you don't want the formula to be case-sensitive, replace FIND with SEARCH.
Hope this helps!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks