CODEWITHSUNDEEP
excelsearchmatchcategories
chloe
chloe

Reputation: 115

EXCEL - Find category by searching keyword from other worksheet

I want to get sales by category (states).

In Sheet1, there're state's names in row A (A1 to A6) and cities in the column. Each cities belongs to states,

e.g. Sydney belongs to NSW, and Melbourne & Geelong belong to VIC.

enter image description here

Then Sheet2 contains sales data like shown below. I want to calculate sales by state each month. At the moment, each cells between row D and row I has a formula like below;

(e.g. F5)

=IF(AND(ISERR(SEARCH("Brisbane",B5)),ISERR(SEARCH("Gold Coast",B5)),ISERR(SEARCH("Cairns",B5))),"",C5)

enter image description here

But I'm sure there would be better and more simple nifty way to do this. As shops increase, this current formula gets more complicated. So it's needed to be optimized.

I would like to make these calculation simple using vlookup or something.. but no luck so far. Any advice, detailed if possible, would be greatly appreciated! Thank you in advance :)

Upvotes: 1

Views: 789

Answers (1)

Captain
Captain

Reputation: 2218

You can do this with an array formula. For example, in D3 use:

=IFERROR(IF(INDIRECT("Sheet1!A"&MAX(IF(ISERROR(SEARCH("*"&Sheet1!$B$1:$D$6&"*",$B3))+(Sheet1!$B$1:$D$6=""),0,ROW(Sheet1!$B$1:$D$6))))=D$1,$C3,""),"Not found")

(To enter an array formula, you need to press Ctrl+Shift+Enter)

  • The SEARCH looks at the store name in B3 and compares it to all names in Sheet1!B1:D6 with a * wildcard before and after. This won't be an error if it matches.
  • Adding the +(Sheet1!$B$1:$D$6="") gives an OR the cell is blank (otherwise ** would match).
  • If it isn't an error, we'll get the ROW of the corresponding match and we take the highest (MAX) row match.
  • We then use INDIRECT to get the cell value in column A of Sheet1 (the State code) and compare that to the state in the top row of our column
  • This is wrapped in an IFERROR to tell us if our store doesn't match any city

Array formulas are a great tool, but a little confusing! Rather than work on a single cell, array formulas work on each cell of an array. Typically this is for counts, sums, averages, etc. By including “logic maths”, you can do some really strange things in a single formula. A good resource is [link]http://www.cpearson.com/excel/arrayformulas.aspx Always remember to use Ctrl+Shift+Enter or really strange things will happen!

Upvotes: 1

Related Questions