Index/Match with 2 match columns, non-exact match

Hello and welcome to the forum.

VLOOKUP is not a volatile function.

I can't picture it in my head. I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

63falcondude, I uploaded a sample file to the original post, along with 2 examples of desired results in the text of the post.

You're right, VLOOKUP is not a volatile function. However I seem to remember that INDEX/MATCH runs quite a bit faster than VLOOKUP with sorted data, and speed is a concern given the ultimate intended use.

If you want to do this with a helper column and a simple INDEX MATCH formula, you can do it like this:

Purchase Date in J2
Stock in K2
Price in L2
Drop down with "or more" and "or less" in M2

F2 =AND(A2=K$2,B2>=J$2,IF(M$2="or more",C2>=L$2,C2<=L$2))
Drag the formula down column F. This is your helper column.

Then you can use this in N2:
=INDEX(B:B,MATCH(TRUE,F:F,0))

See attachment.

Fantastic, thank you! That's a better solution than I thought possible. It's simple and easy to understand, and reduces helper columns from 2 to 1 rather than increasing them. Bravo and many thanks!!

Happy to help! I'm glad you like it.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Spoke too soon. There's a requirement I forgot to mention when simplifying the problem. It has to solve for more than one case simultaneously.

I uploaded a new sample showing 5 cases to solve for. In real life application the are hundreds of cases, so adding helper columns for each one would be quite clumsy. I suppose that could be done without a better approach.

Any thoughts?

Okay, I thought that you would be changing the cells in J2:M2. You can clear the helper column.

Here's one formula that you can use in N2:

=MIN(IF((B$2:B$31>=J2)*(A$2:A$31=K2)*IF(M2="or more",C$2:C$31>=L2,C$2:C$31<=L2),B$2:B$31)) Ctrl Shift Enter

Since you have Office 365, MINIFS is available to you so this should work as well (untested since I do not have this function):

=MINIFS(B$2:B$31,B$2:B$31,">="&J2,A$2:A$31,K2,C$2:C$31,IF(M2="or more",">=","<=")&L2)

That last solution with MINIFS works!

So I made the problem more complicated, and you made the solution even more elegant than your last! (No helper column needed.)

Very impressive. Thank you so much. I'll mark this Solved.

You're welcome. Glad we could help.

Thanks for the rep!