Formula which detects either exact name or partial name

Hi, all

I have a table in Loss sheet which comprises of Parameter, Unit, Budget, Actual & the Deviation. Another sheet called Query is a raw data table which comprises of data of budget & actual with different parameters for different shops.

I would like to construct a formula in which it detects the exact shop name from column B in Query or just its partial from column A (could not change the raw data table, it must be in this form, sorry), the month, the parameter and also whether it's actual or budget. And the formula will be located in column Budget & Actual in Loss sheet.

I believe a construction of Index & Match formula would help. Anyone may enlighten me?

Thank you in advance for your help. I really appreciate it.

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

It'll do - it is better. Now, you need to put together a workbook in which you manually enter at least a few lines of expected results (properly calculated). At the moment there are yellow sections with no indication of what you expect to see there. Remember: you know what you want, but we can't read your mind.

Which version of Excel do you have? There is no 2015 edition.

Which version of Excel are you using? Mac or PC? There is no 2015 for PC.

Why can't the query produce a table with the shop name filled down? How exactly has that table been produced? PowerQuery?

Somehow I imagine that there is more to this than I am considering, however perhaps this will be of some help.
On the Loss sheet cell K5 is populated using: =IF(ISNUMBER(MATCH(K4,Query!T2:T201,0)),K4,LEFT(K4,1))
Note that the formula references a helper column (T) that has been placed on the Query sheet and which may be moved and/or hidden for aesthetic purposes.
Note that the value of cell K5 is hidden, for aesthetics, by using white font.
The formula that populates the Loss table is:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.