Index match with multiple criteria

[farniajr]

Find a value of time that is satisfied the condition:
B2:B23=H2
C2:C23=I2
Date < 30 of today
Average is D2:D23-J2<=±5

*Answer is highlighted in yellow (output is time)

Please see attach file.Please help. Sorry for inconvenience in my first post.
Thank you

[avk]

Not clear idea. But i assume you required result in J2. with two criteria column b&c, from today date but less than 30 days.
Try with in "J2"

Please Login or Register  to view this content.

[Bo_Ry]

Please try
if no duplicate date

Please Login or Register  to view this content.

of this if any duplicate date

Please Login or Register  to view this content.

[farniajr]

Not clear idea. But i assume you required result in J2. with two criteria column b&c, from today date but less than 30 days.
Try with in "J2"

Please Login or Register  to view this content.

-Thanks sir but my needed output is time in time column.

[farniajr]

Please try
if no duplicate date

Please Login or Register  to view this content.

of this if any duplicate date

Please Login or Register  to view this content.

-Thanks sir for help but its still not working

[Bo_Ry]

Sorry, I thought you want 303 for answer.

index.jpg

[FioreMatto]

Buongiorno
nella cella di output

=CERCA(2;1/((B2:B23=H2)*(C2:C23=I2)*(D2:D23>=J2-5)*(D2:D23<=J2+5));E2:E23)

in inglese

=LOOKUP(2,1/((B2:B23=H2)*(C2:C23=I2)*(D2:D23>=J2-5)*(D2:D23<=J2+5)),E2:E23)

[farniajr]

Sorry, I thought you want 303 for answer.

Attachment 599136

-yes sir.303 is answer..i copied your formula above,but why is it not working in my sheet?but you got the answer in your picture.

[farniajr]

Sorry, I thought you want 303 for answer.

Attachment 599136

Capture2.JPG please see attached file

[Bo_Ry]

I'm not sure.

then, Please see attached

[farniajr]

I'm not sure.

then, Please see attached

Still the same sir.I don't know why.please see attachedCapture3.JPG

[Bo_Ry]

I see, you don't have aggregate function, but it available from Excel 2010


Then try this with Ctrl+Shift+Enter

=INDEX($A$2:$A$23,MATCH(MIN(TODAY()-E2:E23*(B2:B23=H2)*(C2:C23=I2)*(ABS(D2:D23-J2)<=5)*(TODAY()-E2:E23<30)),INDEX(TODAY()-E2:E23,),))

=INDEX($A$2:$A$23,MATCH(MIN(TODAY()-E2:E23*(B2:B23=H2)*(C2:C23=I2)*(ABS(D2:D23-J2)<=5)*(TODAY()-E2:E23<30)),TODAY()-E2:E23/(B2:B23=H2)/(C2:C23=I2)/(ABS(D2:D23-J2)<=5),))

[farniajr]

I see, you don't have aggregate function, but it available from Excel 2010


Then try this with Ctrl+Shift+Enter

=INDEX($A$2:$A$23,MATCH(MIN(TODAY()-E2:E23*(B2:B23=H2)*(C2:C23=I2)*(ABS(D2:D23-J2)<=5)*(TODAY()-E2:E23<30)),INDEX(TODAY()-E2:E23,),))

=INDEX($A$2:$A$23,MATCH(MIN(TODAY()-E2:E23*(B2:B23=H2)*(C2:C23=I2)*(ABS(D2:D23-J2)<=5)*(TODAY()-E2:E23<30)),TODAY()-E2:E23/(B2:B23=H2)/(C2:C23=I2)/(ABS(D2:D23-J2)<=5),))

Its working sir..
THANK YOU SO MUCH FOR THE HELP.

[FioreMatto]

Buongiorno nella mia formula non avevo messo il criterio data

=CERCA(2;1/((B2:B23=H2)*(C2:C23=I2)*(D2:D23>=J2-5)*(D2:D23<=J2+5)*(OGGI()-E2:E23<30));E2:E23)

[farniajr]

Buongiorno nella mia formula non avevo messo il criterio data

=CERCA(2;1/((B2:B23=H2)*(C2:C23=I2)*(D2:D23>=J2-5)*(D2:D23<=J2+5)*(OGGI()-E2:E23<30));E2:E23)

Thank you so much sir for the help..I will also try

[farniajr]

I see, you don't have aggregate function, but it available from Excel 2010


Then try this with Ctrl+Shift+Enter

=INDEX($A$2:$A$23,MATCH(MIN(TODAY()-E2:E23*(B2:B23=H2)*(C2:C23=I2)*(ABS(D2:D23-J2)<=5)*(TODAY()-E2:E23<30)),INDEX(TODAY()-E2:E23,),))

=INDEX($A$2:$A$23,MATCH(MIN(TODAY()-E2:E23*(B2:B23=H2)*(C2:C23=I2)*(ABS(D2:D23-J2)<=5)*(TODAY()-E2:E23<30)),TODAY()-E2:E23/(B2:B23=H2)/(C2:C23=I2)/(ABS(D2:D23-J2)<=5),))

Sir Good day,
the formula above is working on first data I sent yesterday.But when I add more datas,I also edited your formula and now its not working.can you help?Please see attached file.Thank you.

[Bo_Ry]

Please try with Ctrl+Shift+Enter

Please Login or Register  to view this content.

There's error #DIV/0! in D34, iferror added to ignore that cell
or just delete D34

[farniajr]

Please try with Ctrl+Shift+Enter

Please Login or Register  to view this content.

There's error #DIV/0! in D34, iferror added to ignore that cell
or just delete D34

Its working Sir.Thanks much.

[farniajr]

Please try with Ctrl+Shift+Enter

Please Login or Register  to view this content.

There's error #DIV/0! in D34, iferror added to ignore that cell
or just delete D34

Hi Sir.Im enocunter problem again..It matches though not all conditions were followed.Please see attach file.
Please help me.

[farniajr]

I got it sir.I added another array for another condition

[AliGW]

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

[Bo_Ry]

There are duplicate date,
Use this With CSE

Please Login or Register  to view this content.

[farniajr]

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

Okay sir..Noted.Thank you.
Thanks much for all your help.God Bless!

[farniajr]

There are duplicate date,
Use this With CSE

Please Login or Register  to view this content.

I will try sir.Thank you