index match with multiple criteria to create list

[Bob_ipc]

Hello,

I find myself needing some help on a problem I am having. I am looking to get a list from my Entry tab that meets two criteria, the two criteria are the date and Project Name. I am trying to use Index Match but have not yet gotten very far. Can anyone help?

Thanks in advance

 

[Bob_ipc]

That still doesn't explain why @Cubist's formula in post #2 wasn't working for you. You don't need to specify the worksheet name when the formula resides on that same worksheet.

=FILTER(Entry!C4:C13,(Entry!A4:A13=B2)*(Entry!B4:B13=J6),"Not found")

...should still work.

I’m not sure what the difference is, I’ll fool around with it some more tomorrow, I really appreciate your guys help! I’m always impressed how well you guys know excel! So much to learn with this program. Cheers

 

[KRice]

We're glad you got it working....happy to help.

 

[Bob_ipc]

Hey guys, Another question... How would you change this formula to ignore blanks in Entry!C4:C13?

 

[Cubist]

Try

=FILTER(Entry!C4:C11,(Summary!$B$2=Entry!A4:A11)*(Summary!$J$6=Entry!B4:B11)*(Entry!C4:C11<>""),"Not found")

 

[Bob_ipc]

Try

=FILTER(Entry!C4:C11,(Summary!$B$2=Entry!A4:A11)*(Summary!$J$6=Entry!B4:B11)*(Entry!C4:C11<>""),"Not found")

You make it seem so easy! haha works perfect! I was sort of on the right track with adding <>"" to Entry!C4:C11 but i was doing it at the beginning which did not work. Like this =FILTER(Entry!C4:C11,(Entry!C4:C11<>"")*(Summary!$B$2=Entry!A4:A11)*(Summary!$J$6=Entry!B4:B11),"Not found")

Thanks again for the Help!!

 

[KRice]

A couple of finer points to add. Since the formula is being placed on the Summary worksheet, and the formula refers to cells B2 and J2 on the same Summary worksheet, you don't need to specify the worksheet name in the cell references. And regarding the most recent filtering criteria (Entry!C4:C11<>""), the order doesn't matter. All three of those filtering criteria are arrays that are being multiplied together, and multiplication doesn't depend on order...so you could place (Entry!C4:C11<>"") first. I prefer @Cubist's order...A, B, C, but that's only a personal preference. If you are entering this formula in only one place and allowing the results the "spill", then it is not essential to "lock" the cell references (the $ signs), but if the formula is to be moved, then you will probably want to preserve the referenced cell ranges by locking them. Here is what the formula would look like trimmed down without the unnecessary parts:

=FILTER(Entry!C4:C13,(Entry!A4:A13=B2)*(Entry!B4:B13=J6)*(Entry!C4:C13<>""),"Not found")

 

[Bob_ipc]

A couple of finer points to add. Since the formula is being placed on the Summary worksheet, and the formula refers to cells B2 and J2 on the same Summary worksheet, you don't need to specify the worksheet name in the cell references. And regarding the most recent filtering criteria (Entry!C4:C11<>""), the order doesn't matter. All three of those filtering criteria are arrays that are being multiplied together, and multiplication doesn't depend on order...so you could place (Entry!C4:C11<>"") first. I prefer @Cubist's order...A, B, C, but that's only a personal preference. If you are entering this formula in only one place and allowing the results the "spill", then it is not essential to "lock" the cell references (the $ signs), but if the formula is to be moved, then you will probably want to preserve the referenced cell ranges by locking them. Here is what the formula would look like trimmed down without the unnecessary parts:

=FILTER(Entry!C4:C13,(Entry!A4:A13=B2)*(Entry!B4:B13=J6)*(Entry!C4:C13<>""),"Not found")

Thanks for the tips I really appreciate it