Lookup function two fixed and one conditional matches

[Winship]

Good Morning, Excel Forum World.

See the attached file.

Step One: TWO Cell Exact Match. Seek formula in yellow highlighted cells in Table 1 seeking exact match of column A and B items to items within Table 2 column H and I.

Step Two: Conditional Lookup. Enter soonest time from column J exceeding or equalling the column C time but by not more than the time entered in cell M:16.

Enter Zero (0) if there is no match meeting these conditions.

NOTE: The formula entered in the E column answers a different question. It asks whether the matching number is the next higher number and enters 0 if it is not the next higher number, apparently ignoring the two cell exact match condition. Example: See cell E:22. The desired answer number for E:22 is in D:22, entered manually. However, the number in J:34 is causing the formula answer to be ZERO, because the desired number is the second number greater than the c:22 number and even though the two matching conditions are not met.

Any way to do this without decending or ascending sort, or with standard non array formula?

Thanks so very much

[JBeaucaire]

In K1, then copied down:
=H17&"-"&I17

In E17, enter this array formula:
=MAX(IF(A17&"-"&B17=$K$17:$K$32, $J$17:$J$32))
...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[JBeaucaire]

Or maybe this array formula:

=IFERROR(INDEX($J$17:$J$32, SMALL(IF(($K$17:$K$32=A16&"-"&B16)*($J$17:$J$32>=C16), ROW($J$17:$J$32)-16, ""), 1)), 0)

[Winship]

Thanks for the two options.

Having trouble with both.

Couple of things. Need not less than the time in column C. And need reference to m:16 as a max on the condition.

The original formula was close, but it defaulted to zero if the "right" answer was not the lowest number exceeding the threshold, seemingly ignoring the fixed conditions requirement for items in a and b columns.

[dredwolf]

Try this regular formula, it's a bit long, but works with the data provided :
In C17:

Please Login or Register  to view this content.

Drag down

Hope this helps

[dredwolf]

Or this Array Formula (Cntrl+Shift+Enter,not just Enter) in c17:

Please Login or Register  to view this content.

Drag down

Edit-
Added workbook, both formulas are in the green shaded cells

Hope this helps

[Winship]

Dear Dredwolf:

Looks great. Works perfect in the model. I did change the H,I and J reference row from 32 to 35. Also changed the data for testing purposes. Will examine function in real file. Once I do this, I'll get back to forum and hopefully mark this one as solved.

Thanks you so much. This is really awesomest!

[dredwolf]

You are welcome

[Winship]

Thanks for all the help with this query. Getting closer for sure.

General Objective: Examine the times in column C. Search for the time in column J that equals or exceeds the C column time, but which is also less than the value in M1 plus the C column time, and which also has an exact match in the A, B and H, I columns. The formula is to be placed in column E at cell E17 downward.
Update: The formula has been pasted to and copied to E17:E33. The formula works as long as there are no column A and B duplications in Table 1. I apparently had no column A, B, H & I duplications in the original file. It appears that Column H and I duplications impact the results too, but only if there is a greater time value. (Copy the contents of L22:M22 to H22. The change results in a Zero value in cell E21. The lower time value at J30 does not impact the E21 result standing alone). The two table below have been modified to create duplications A and B duplications in Table 1 and H & I duplications in Table 2. The fill colors visually direct the viewer to the matching desired answers. If the reconcile column (F) is greater than zero, then there is an error.

[dredwolf]

Try this instead:
in E17:

Please Login or Register  to view this content.

Drag down

this is a lot shorter than the original

Hope this helps

[Winship]

Dear dredwolf: Revised model works perfectly in the model. Will examine formula in "real" file. So far so good. Will mark as solved once proffing completed. Thanks so much for your help with this. Take the rest of this Sunday off!

[dredwolf]

You are welcome
Here's crossing my fingers for the "real" data test
And this is how I relax...LOL..

[Winship]

It works. Yes! Thank you all!

[dredwolf]

You are welcome