Excel: Trouble with INDEX-MATCH returning incorrect row when using multiple criteria
I keep running into I'm following best practices but I've searched everywhere and can't find a clear answer. I'm writing unit tests and I'm working on a project and hit a roadblock... I'm working on a project and hit a roadblock. I'm having an scenario with using the `INDEX` and `MATCH` functions together to retrieve data from a table based on multiple criteria. My dataset consists of sales records with columns for `Date`, `Region`, `Product`, and `Sales Amount`. I want to find the `Sales Amount` for a specific `Product` in a specific `Region` on a specific `Date`. Here's the formula I tried: ```excel =INDEX(D2:D100, MATCH(1, (A2:A100="2023-10-15") * (B2:B100="North") * (C2:C100="Widget A"), 0)) ``` However, I’m getting a `#N/A` behavior, and I suspect it's because my criteria might not be matching correctly. I've checked the formatting of the `Date` column, and it appears to be in the correct `Date` format. I also validated that the `Region` and `Product` columns do indeed contain the specified values, but I’ve noticed that there are leading spaces in the `Product` names which might be causing issues. I tried using the `TRIM` function in a helper column to clean up the `Product` names, but my formula is still returning `#N/A`. I then attempted the following: ```excel =INDEX(D2:D100, MATCH(1, (A2:A100=DATE(2023,10,15)) * (B2:B100="North") * (TRIM(C2:C100)="Widget A"), 0)) ``` This also resulted in `#N/A`. Is there a better approach to handling this scenario, or am I missing something in the formulation of my `INDEX-MATCH` combination? Any insights or alternate methods would be greatly appreciated! I'm working on a service that needs to handle this. Any help would be greatly appreciated! What am I doing wrong? This is happening in both development and production on CentOS. Hoping someone can shed some light on this. Thanks for taking the time to read this! My team is using Excel for this web app. Any examples would be super helpful.