Forum Discussion
A formula that labels all convenience stores.
Hi There, this is my first post.
Using Office 365 and a fairly new, but excited, Excel user. 🙂
I'm trying to make a smart excel sheet to keep track of my economy and I immediately ran into at problem I can't seem to solve.
All the yellow marked cells are transactions from the same shop except the last one. First I made a dropdown list to label all transactions a category. But there must be a smarter way to do it, but every transaction has a unique Name/number so I am looking into "wildcards"??
Netto is a convenience store, and so is Meny in C52. "Mad & Drikke" is Food.
What I would like to achieve is a formula that labels all convenience stores, from a list "food"
So this is the list of convenience stores that I would like to be labeled "Food" in the other sheet in column E. I found that =IF(ISNUMBER(SEARCH("Netto",C47)),"Mad & Drikke","") will print "Mad & Drikke" in a cell adjacent to Netto, but that is as far as I got.
Anyone with a solution?
Regards Kasper
As variant you may create helper range as on the left
and return category by
=INDEX( $C$3:$C$7, XMATCH(TRUE, ISNUMBER( SEARCH($B$3:$B$7, E3) ) ) )
2 Replies
As variant you may create helper range as on the left
and return category by
=INDEX( $C$3:$C$7, XMATCH(TRUE, ISNUMBER( SEARCH($B$3:$B$7, E3) ) ) )Thanks the formula works perfect.
But what if I want to make things a bit easier for my self by having all categories listed in a row and all transactions listed underneath? Like in this picture. I tried the =SEARCH function on the whole table but the problem is that it returns "1" for all the blank cells also.
Is there a function that ignores blank cells so that I can search for Mc Donalds and return the tekst "Junkfood"??
Regards Kasper
