Excel Formula Help 2

[arvarr]

Seeking help around Excel formula.
I have 3 combinations - A,B & C that are number ranges.
I have broken them down into Lower and Upper.
Numbers that fall within Range A, Range B and Range C will give the expected Outcome.
Attached is an example workbook with subset of criteria.
Thanks,
Arv

 

[SkipVought]

Hi,

I've given you an approach that could be part or all of a solution: SUMPRODUCT.

But you may have some issues with your criteria table like...
1) 2 Outcome 16 rows
2) the criteria in Outcome 16 & the-would-be-Outcome 17 overlap!

Also your 4-line example has no valid Outcome 2

So, at this juncture, TILT!!!

Back to the drawing board.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

So here's come corrections in your criteria table & solution uploaded.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[arvarr]

Hi Skip
Yes, my bad with the table criteria.
Woohooo...Thanks for the formula.
Impressive as ever
Thanks,
Arv

 

[arvarr]

 

[combo]

It's ok, 2+3-1=4. Skip's formula works with either single or no match row entry.

IMHO such excel formulas, at least applied to the whole process, are hard to manage, esp. when you change assumptions. Moreover, you can't get more that single output easily. It is beter, from the point of robustness, to have some helper columns to process the data. Personally I would consider (if using excel) switching to power query environment (get & transform excel data section, built in excel since 2016 version). It is more natural and can give multiple match output, having both tables as input. In practice it is one of new BI applications implemented in excel, a powerful tool for data transforming, definitely worth to learn it.

combo

 

[SkipVought]

In the example you uploaded, you have ambiguous criteria. Which is the reason for your question.

Since this is a different question than the original, you ought to
1) delete the last 2 posts and
2) start a new thread.

If you want to allow multiple results, then a solution like combo suggested is possible, but then a simple "example" table with one slot is inadequate for reporting results. I would suggest a slightly different solution using MS Query. But that all should be in a new thread. Different question.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[arvarr]

Hi combo
Are you able to provide example of the solution in power query so that i can refer to the data query setup?
Thanks,
arv

 

[arvarr]

HI Skip
Sure, i will post in new thread.
Thanks,
arv

 

[combo]

Continuing with the file you moved to new thread, I added a new sheet with Power Query solution. You need excel 2016 or higher.
To work with current data (Test_Book2), you need to enable connections if you are asked to.

The file contains:
1. criteria table with query to get the data in PQ environment (not necessary to work this way, but it profits in some situations, no need to name the query as source table),
2. input table with query to get the data in PQ,
3. query that processes input (queries) and criteria, with output to output table.

All you have to do is to fill input and criteria in tables and refresh output table (select any cell inside, right-click and refresh).
Don't change table names or headers unless you modify queries.

The single output query, line by its line:
1. appends criteria table to each input line,
2. expands criteria (so you have a product of tables),
3. tests A, B and C, combines tests,
4. filters final tests,
5. removes unnecessary input and helper columns.

combo

 

[arvarr]

Hi combo
PQ > great feature to learn.
It looks like if input data does not fall into the table criteria, it omits the data in the output table.
Is there anyway to include and highlight those records?

Let me play around with the file and may come back with questions.

Thanks,
arv

 

[combo]

As I wrote, the query creates table1 x table2 product and filters matching data. This is what I planned, to handle duplicated criteria matched.

To get all inputs, you could start new query from input data, add the query I created with external join (+expanding rows), remove unnecessary columns and replace Nulls (in output when no criteria match). For duplicated output you get multiple rows, you have to decide, how to handle this: you may leave the query as connection only (no worksheet output) and use it as input for pivot table (from external data source, you can find the query in workbook connections).

combo

 

[arvarr]

Hi combo
Noted, let me give it a shot.
Thanks for your help ,
arv

 

[combo]

You can find a quick extension of the output query from my recent file, without renaming steps, most is either recorded or mouse actions.

combo

 

[arvarr]

Thanks Combo. I shall give it a shot