Multiple IF condition based on range

**srct2000** · 10-31-2019, 07:24 AM

Hi

I have the following Excel formula which is trying to return the oldest date from a multiple criteria:

{=MIN(IF(A:A=W1,IF(Q:Q=X5,O:O)))}

Column A contains the customer number which is matched here by cell W1 (Col A also contains multiple incidences of the same customer number so can appear many times in the list).

Column Q contains product codes

Cell X5 contains a specific product code however I wish this to match against 8 other codes in a list. Unfortunately if you specify a range it isn't returning the correct data - i.e. {=MIN(IF(A:A=W1,IF(Q:Q=X1:X9,O:O)))}

Column O contains the dates of which I wish to report the oldest based on a match being made on the product code for that customer number.

Any help or advice would be much appreciated.

Many thanks

Stephen

**AliGW** · 10-31-2019, 07:29 AM

Welcome to the forum.

You will need to give us some idea of the nature of the product codes. Do they have similar prefixes, for instance?

Will you please attach a small sample Excel workbook?

Instructions (Please Read Carefully):

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

**srct2000** · 10-31-2019, 07:31 AM

Hi, many thanks for your quick reply.

The product codes are all alphabetical as follows:

CHAIMO
COMIMO
ENGDON
ENGIMO
IMOCOL
IMODON
IMOLBA
IMOMTL
IMOSUN

and are listed in cells X1 to X9 on the spreadsheet.

Many thanks.

**AliGW** · 10-31-2019, 07:32 AM

Thanks. When you have attached the workbook, I will have a look.

**srct2000** · 10-31-2019, 07:47 AM

Many thanks again. Sorry, I'm new to the forum; can you confirm how I can attach the workbook please?

**AliGW** · 10-31-2019, 07:50 AM

Clear instructions were given in post #2 towards the bottom.

**srct2000** · 10-31-2019, 07:53 AM

Sorry, got it now. Hopefully this upload will work.

**AliGW** · 10-31-2019, 07:55 AM

Sorry, this is not as much help as I had hoped.

Please re-read point #2 of my instructions above and amend the workbook, then reattach it. Thanks.

**srct2000** · 10-31-2019, 08:03 AM

Sorry, I was using a stripped down version of the original file. I've attached the original but taken out certain columns. Obviously this has adjusted the aforementioned cell formulas a bit. Is this OK?

Many thanks.

**srct2000** · 10-31-2019, 08:04 AM

Sorry, I need to remove the password. I'll re-upload...

**srct2000** · 10-31-2019, 08:06 AM

OK, try again - sorry!

**AliGW** · 10-31-2019, 08:34 AM

Sorry - where are the mocked up results? What do the cells in red represent? I'm really not getting what you are trying to do and I don't know the answers you are expecting.

**srct2000** · 10-31-2019, 08:48 AM

For each customer listed in Column A, I wish to deduce their oldest and newest orders matching only the product codes listed in column J (and not one specific product code as my example is currently demonstrating).

Column A contains all customer numbers, most of which are duplicated due to multiple orders. I have de-duped these numbers and listed them in column I (i.e. to act as a look-up).
Column B is the date of their order
Column C is the product code for the order

So for each customer I want to know their oldest and newest orders but only using the product code range listed in column J (i.e. J1 - J9)

Customer 471 therefore would have 27/04/2019 as the oldest date (based on IMOCOL product code) and 20/07/2019 as the newest date (based on IMODON) - all other codes appearing in column C for 471 would be ignored as they don't appear in the range identified in column J.

Sorry, the formulas have changed as I removed columns from the original file.

{=MIN(IF(A:A=I1,IF(C:C=J5,B:B)))}
{=MAX(IF(A:A=I1,IF(C:C=J5,B:B)))}

These formulas do work but the problem is IF(C:C=J5) - this is only referring to one specific code as opposed to being matched against the range in column J (cells J1-J9).

Does this help?

Many thanks

**AliGW** · 10-31-2019, 08:53 AM

Ah, I see! Then surely you just need this:

{=MIN(IF(A:A=I1,B:B))}

**srct2000** · 10-31-2019, 09:03 AM

Wouldn't this just return the oldest date value regardless of associated product code? I would need to ignore anything that wasn't CHAIMO, COMIMO, ENGDON, ENGIMO, IMOCOL, IMODON, IMOLBA, IMOMTL or IMOSUN.

Many thanks

**AliGW** · 10-31-2019, 09:05 AM

OK - so how is Excel meant to know which product codes to include? Where is that defined?

**srct2000** · 10-31-2019, 09:08 AM

This is exactly what I need to know how to do. Where/How can I define this range?

**AliGW** · 10-31-2019, 09:10 AM

I see - OK, this is making sense now.

What is it about these particular codes that is different to the ones you want to exclude? If this is an indiscriminate thing, then you will need to have a list somewhere that identifies the codes that need considering.

**AliGW** · 10-31-2019, 09:20 AM

Just had another look. Is this what you are trying to do?

=MIN(IF(A:A=E2,IF(C:C=VLOOKUP(E2,$I$1:$J$1087,2,0),B:B)))

If so, then the code in the lookup table needs to be the one you are looking for. In the sample workbook customer 59 has no CHAIMO code ...

**srct2000** · 10-31-2019, 11:10 AM

Many thanks for the formula - I'll give this a go. Yes I'm basically just trying to determine a distinct set of product codes from all the available ones in the raw data.

**srct2000** · 10-31-2019, 11:40 AM

Unfortunately the formula is just returning a 0 value.

Does the VLOOKUP have to also include the values in column I? For example, I'm not looking to specifically match customer 59 with CHAIMO alone but with all the codes specified in column J.

Again, with customer 471 Excel should only be returning MIN and MAX date values for the entries in bold below:

471 11/11/2016 LALDON
471 29/11/2016 RAFDON
471 30/01/2017 LALDON
471 30/01/2017 RAFDON
471 10/05/2017 RAFDON
471 05/07/2017 RAFDON
471 13/11/2017 RAFENT
471 13/11/2017 RAFDON
471 15/11/2017 LALDON
471 09/01/2018 RAFDON
471 09/01/2018 LALDON
471 27/04/2018 IMOCOL
471 03/11/2018 LALDON
471 24/12/2018 LALDON
471 01/05/2019 IMODON
471 28/05/2019 RAFENT
471 20/07/2019 IMODON

**AliGW** · 10-31-2019, 11:46 AM

OK - but WHERE in the workbook have you associated customer 471 with those two codes?

I'm really not sure from your workbook. I'm having to guess all the time.

I think we should start again.

Please tell us:

1. Which codes you wish to count (products).
2. Whether these are the same for ALL customers (shared list), or whether each customer needs their own list of codes to count.
3. How you want the results to be presented.

I am sorry, but what is blindingly obvious to you is not coming across as such to me.

**srct2000** · 10-31-2019, 11:54 AM

I'm not associating specific customers to specific codes. I only want to include the orders associated with those product codes for everyone on the list.

1. Which codes you wish to count (products).
Only entries associated with CHAIMO, COMIMO, ENGDON, ENGIMO, IMOCOL, IMODON, IMOLBA, IMOMTL and IMOSUN

2. Whether these are the same for ALL customers (shared list), or whether each customer needs their own list of codes to count.
The same for all customers

3. How you want the results to be presented.
Oldest order date Newest order date
471 dd/mm/yyyy dd/mm/yyyy

**AliGW** · 10-31-2019, 11:56 AM

OK - right. Let me have a quick think. I'll get back to you shortly. Although someone may well beat me to it!

**romperstomper** · 10-31-2019, 12:35 PM

Do you mean:

=MIN(IF(A:A=I1,IF(ISNUMBER(MATCH(C:C,$J$1:$J$9,0)),B:B)))

**XLent** · 10-31-2019, 12:35 PM

one option - array based

=MIN(IF(ISNUMBER(MATCH(C2:C3763,$J$1:$J$9,0)/(A2:A3763=E2)),$B$2:$B$3763))
confirmed with CTRL + SHIFT + ENTER

or you can revert to an aggregate

=AGGREGATE(15,6,$B$2:$B$3763/(A2:A3763=E2)/MATCH(C2:C3763,$J$1:$J$9,0),1)
confirmed with enter

for the largest switch array to MAX and AGGREGATE to 14 rather than 15

avoid entire column references with either approach -- use a Table, or a Dynamic Named Range (or explicit range with limited excess)

edit: ha, I might have been looking at an old sample but in the above E2 refers to "59", J1:J9 hold codes "for inclusion", and A:C the values being searched.

**srct2000** · 10-31-2019, 01:00 PM

rorya, that looks to have done the trick... thank you very much!

Thank you everyone for your suggestions; it's much appreciated.

Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Re: Multiple IF condition based on range

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA to add Checkboxes Based on Condition in Another Range

[SOLVED] How to sum range based on condition

[SOLVED] dynamic range specification (based on column header) in sumproduct multiple condition

Bookmarks

Bookmarks

Posting Permissions