Match fields and return more than 1 result

[Tally04]

Hi There,


I want to create a simple result sheet, so when a "job" comes in, I can quickly match which companies (more than one in some cases) can cover it. Based on location, what type of activity is needed, and the services they offer

My real data has various more factors in finding a matching company. I am not even sure what I can use that would give me a simple result

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

[Tally04]

Apologies, My sample didn't attach, I thought I caught and fixed it before anyone read my post lol.

[Glenn Kennedy]

I think a bit more info is needed.

If the location is OK... but only ONE of the services specified is offered... return a blank for that company... or retun its name... or retun it in another column (labelled "close match")... or what

[nick.williams]

I was thinking about this one and it might be easier if the data on the companies didn't have duplicated columns.

eg instead of:

Company1...Loc1...Loc2...Service1...Service2

You have 4 rows:

Company1...Loc1...Service1
Company1...Loc1...Service2
Company1...Loc2...Service1
Company1...Loc2...Service2

But not sure if that's feasible.

[Glenn Kennedy]

Separate services needed into two columns:

=LET(Co,Table1[[Company]:[Company]],S,Table1[[Services offered 1]:[Services offered 2]],Cy,Table1[[Location covered 1]:[Locationcovered 2]],IFERROR(INDEX(Co,AGGREGATE(15,6,ROW(Co)-MIN(ROW(Co))+1/(--(MMULT(--(Cy=$B5),{1;1})*(MMULT(--(S=$C5),{1;1})+IF($D5="",ROW(Co)^0,(MMULT(--(S=$D5),{1;1}))))=2)),COLUMNS($E5:E5))),""))