If Cell Contains word from a range then, E10, else, "None"
Basically, I would like the formula to accomplish the following:
If A3 contains any of the words from E11:E20 (aka, Table defined as "Groceries"), then display the contents from E10. If not, then "None".
Can this be done without VBA?
If not, I need help as I have always been able to accomplish anything that I needed to in Excel without VBA... until now.
Thanks! :-)
Try:
=IF(ISNUMBER(LOOKUP(9.999999+307,SEARCH($E$11:$E$14,A3))),$E$10,"")
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
PERFECT!... I have now expanded and repeated this formula to autonomously determine an expense category based on the text in the transaction description line.
Thanks so much! Care to elaborate as to why the phony number lookup was needed... ?
thanks again either way!
I missed an Exponential in there:
s/b =IF(ISNUMBER(LOOKUP(9.999999E+307,SEARCH($E$11:$E$14,A3))),$E$10,"")
So basically 9.999999E+307 represents the biggest number Excel can work with.
Lookup() looks for the last entry in a search array that is smaller than or equal to the lookup value (the big number). The SEARCH() function searches each of the entries in E11:E14 and tries to find them in A3. If a match is found the position number within A3 is returned... where a match is not found, we get the #VALUE! error... Lookup will return the last value that is less than or equal to the big number, which will be the last position return of any matched information)... then the Isnumber() checks if there was a numeric result returned from LOOKUP (which would have returned the error if no matches were found).
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks