Excel 2007 : Return Value Based on Multiple Criteria

[pmhxcel]

Hi there,

I've heard such good feedback from co-workers about this Forum that I needed to join myself. I work with Excel a lot but dealing with with multiple criteria is something new for me.

I'd appreciate help to develop a formula or suitable solution to extract a single date for multiple categories where I need date per Category of Last Batch 2 (if there is Batch 2 in a Category) or date of First Batch 1 (if there is no Batch 2).

Have tried nested IF's and lookups but can't get required dates and I have a deadline for this.

I have attached test data with needed results if you could help. Much appreciate it and thanks

[Cutter]

One way - I have used 2 helper columns (O & P) which contain array formulas (they must be entered using Ctrl+Shift+Enter). The helper columns can be hidden. I also filled in the gaps in column A. Conditional formatting can hide the extra values so that it appears to have gaps if that is what you want.
The dates you had in col N are now derived by a simple IF() formula.
The G to J columns were not used.

Hope this helps - I'm calling it quits for tonight.

[pmhxcel]

Hi Cutter,

You are indeed the Forum Guru! I've never worked with array formulas before and man you rock!!! Thanks for your very quick response. My next task is to apply the formulas now to my data that has about 55000 records. I'll let you know how it goes ...

[Cutter]

First off, sorry I forgot to welcome a fellow Canadian to the forum. There are a few regulars from Alberta here, including 'davesexcel' (a forum moderator).

Secondly, you're welcome BUT - As soon as my head hit the pillow last night I thought of an obviously better way (but was too tired to get back up!)

This eliminates helper columns but still requires no gaps in Category column. Cols G:J are still not used.

A single array formula entered in cell N11 (remember to enter it with CSE (ctrl+shift+enter)) then dragged down from N11 to N17

Please Login or Register  to view this content.

[pmhxcel]

No worries about the welcome and hope to touch base with fellow Canadians. The moment you replied I knew that I'm dealing with very knowledgeable folks. Formula you gave me last night worked perfectly for my very huge file. I will apply it again for another file with about 78K records. Guys at the office couldn't believe that iterative cycles like this formula is possible with Excel, nor Excel in general.

I'll try the one formula you gave me because it will cut down steps. Thanks again. I have a followup one that I'll post tomorrow. Regards,

[pmhxcel]

Hi Cutter,

Haven't really tried new formula yet but I will. The helper cells have been so useful now that I've used them for other criteria and they work like a charm.

Anyway, I've attached the final part of this exercise and the final end result should be a date based on a specific percentage value. Again, data is a list and I've tried vlookup and array formula but can't make it work. With a formula, I don't need to manually look at the data to get the final date.

I've attached a sample spreadsheet capturing the process and final end result (plus some notes). Your help would be appreciated.

Regards,

[Cutter]

In H7 place this formula and dragged down:

=INDEX($A$7:$A$19,MATCH(G7,$D$7:$D$19,1))

This will retrieve the date when the closest value (but not exceeding the value) occurs. So it will work for 49.7 when you're searching for 50 but it would not work for 51.3.

[Cutter]

See if this works for the closest value above or below the target:

In H7 and dragged down:

Please Login or Register  to view this content.

[pmhxcel]

Hi Cutter,

Both equations work perfectly. It will take a while for me to digest the second one though but I'll see how close it gives the values we need vs the first one.

I hope you dont mind if I have some challenging ones for you in the future!

Regards,

[pmhxcel]

Hi Cutter,

You gave me two codes to use to find matching date - I'm now using the longer formula because it actually gives me more flexibility and again - its very accurate. I will use the long formula from here and hopefully everything piece of data captured is what we need.

Regards,

[Cutter]

I'm glad it works for you.

Don't forget to mark your thread as SOLVED (click on the FAQ link at top of page for directions).

[pmhxcel]

Thanks again. I've updated the thread to SOLVED status. If I have a new one I'll open a new thread (if I can't find a similar one). Is there a way for me to notify you just in case?

[Cutter]

Hi pmhxcel

I'll open a new thread (if I can't find a similar one).

I hope you don't mean you'll post in another person's thread if it is similar. That's a no-no. (Click on Forum Rules link and see Rule #2).

As for this:

Is there a way for me to notify you just in case?

I try to check out the New Posts a few times every day but there are plenty of people here that are WAAAAAY better than me at this so your questions will definitely be answered. You can send me a message to alert me to the fact that you have made a new post, though (click on "Cutter" in any of my posts and select from the resulting option list).