Return value with multiple different criteria

[richard.lim85]

HI,

I'm looking for a simple formula on how to return a value with multiple criteria

I have attached a sample excel

Assuming my Promo Name has 50k lines of data and Date Range has about 100 lines of data

i have tried Index Match but doesn't seems working for me

Please advise

Thank You

SIincerely,
Richard

[bebo021999]

What and where is the output?
If I understand correctly, sheet "date range" cell E2 will count the occucrence with 4 criterias?

[WideBoyDixon]

D2 on "Promo Name" sheet:

Please Login or Register  to view this content.

WBD

[richard.lim85]

What and where is the output?
If I understand correctly, sheet "date range" cell E2 will count the occucrence with 4 criterias?

The Return value i'm looking for is Promo Name, the criteria to match is the SKU and Start & End Date

for example Line 4 is HAPPY HOLIDAYS, Line 5 is CHOCO PROMO, despite Same SKU, but date is different, hence the Promo Name return differently

[richard.lim85]

D2 on "Promo Name" sheet:

Please Login or Register  to view this content.

WBD

HI

I copy the formula to D2 in Promo Name sheet, the outcome is blank

Hoping to return the value as HAPPY HOLIDAYS

Please advise

[richard.lim85]

HI

I copy the formula to D2 in Promo Name sheet, the outcome is blank

Hoping to return the value as HAPPY HOLIDAYS

Please advise

Is OK, I understand my mistake there, the date should set after 1st Oct 2021, it works now

THank You

[WideBoyDixon]

Yes - I meant to say that your data was incorrect - I had to change it to return "HAPPY HOLIDAYS". Glad it's working now.

WBD

[richard.lim85]

D2 on "Promo Name" sheet:

Please Login or Register  to view this content.

WBD

HI

I need to understand the logic here ('Date Range'!$C$2:$C$100<=$C2)*('Date Range'!$D$2:$D$100>=$C2)

Why is it <= & >= works but >= & <= not work

my understanding is the value should return when the sales is equal or greater than 1st Oct 2021 AND less than or equal to 31st Dec 2021

How come the other way round works?

Please advise

[WideBoyDixon]

It's check the date in C2 lies between the dates in columns C/D on the Date Range sheet

Please Login or Register  to view this content.

If it makes you happier, re-write it like this:

Please Login or Register  to view this content.

WBD