×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Excel Formula Help2

 Forum Search FAQs Links MVPs

## Excel Formula Help

(OP)
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

### RE: Excel Formula Help

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

### RE: Excel Formula Help

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

### RE: Excel Formula Help

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

### RE: Excel Formula Help

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

### RE: Excel Formula Help

(OP)
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

### RE: Excel Formula Help

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

### RE: Excel Formula Help

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

### RE: Excel Formula Help

(OP)
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

### RE: Excel Formula Help

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

### RE: Excel Formula Help

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

### RE: Excel Formula Help

(OP)
Thanks Combo. I shall give it a shot

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

#### Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!