Forum Discussion
Solved
Index and match #N/A
Greetings everyone!
I am trying to create an index/match where it checks the teaching subject in a certain city and returns the most selling teacher in that area, but it keeps returning #N/A
Here is the function:
=INDEX(Shamal_D[Teacher_name], MATCH(MAX(IF((Shamal_D[Subject]=[@Subject])*(Shamal_D[city]=[@City]), Shamal_D[Sales Count], -1)), Shamal_D[Sales Count], 0))
Please help me.
In addition, I'd adjust the logic a bit. You are searching max sales count for given combination of subject and city. For that max sales count you return Teacher name.
However, same Sales count as found max one could be for another record. If so you return first of the, subject and city criteria are not applied again after the mas was found.
On such sample
your formula
=INDEX(Shamal_D[Teacher_name], MATCH(MAX(IF((Shamal_D[Subject]=[@Subject])*(Shamal_D[city]=[@City]), Shamal_D[Sales Count], -1)), Shamal_D[Sales Count], 0))returns result for top left table.
Bit modified formula
=LET( criteria, (Shamal_D[Subject] = [@Subject]) * (Shamal_D[city] = [@City]), maxSales, AGGREGATE(14, 6, 1 / criteria * Shamal_D[Sales Count], 1), position, XMATCH(1, criteria * (Shamal_D[Sales Count] = maxSales), 0), IFERROR( INDEX(Shamal_D[Teacher_name], position), "wrong data" ) )is in bottom left table.
In addition, I'd adjust the logic a bit. You are searching max sales count for given combination of subject and city. For that max sales count you return Teacher name.
However, same Sales count as found max one could be for another record. If so you return first of the, subject and city criteria are not applied again after the mas was found.
On such sample
your formula
=INDEX(Shamal_D[Teacher_name], MATCH(MAX(IF((Shamal_D[Subject]=[@Subject])*(Shamal_D[city]=[@City]), Shamal_D[Sales Count], -1)), Shamal_D[Sales Count], 0))returns result for top left table.
Bit modified formula
=LET( criteria, (Shamal_D[Subject] = [@Subject]) * (Shamal_D[city] = [@City]), maxSales, AGGREGATE(14, 6, 1 / criteria * Shamal_D[Sales Count], 1), position, XMATCH(1, criteria * (Shamal_D[Sales Count] = maxSales), 0), IFERROR( INDEX(Shamal_D[Teacher_name], position), "wrong data" ) )is in bottom left table.
- Greetings dear,
It worked! and thank you for your cooperation.
Unfortunately, it didn't return all correct answers, it returned the most selling teacher in that city regardless of subject, plus in some cases it returned a teacher name that didn't even teach that subject.
The #N/A error in your formula could be caused by a few different reasons.
Here are a few things you can check:
- Data range: Ensure that the data range Shamal_D[Teacher_name] is correct and includes the teacher names you want to retrieve. Also, verify that the data range Shamal_D[Subject] and Shamal_D[city] correspond to the columns containing the teaching subjects and cities, respectively.
- Array formula: The formula you provided is an array formula because it uses the IF function with array criteria. After entering the formula, instead of pressing Enter, you need to press Ctrl+Shift+Enter to confirm it as an array formula. When entered correctly, Excel will automatically add curly braces {} around the formula.
- Numeric values: Ensure that the data in the Shamal_D[Sales Count] column is numeric. If any cells contain non-numeric values, it can cause the MAX function to return an error.
- No matching values: If there are no matching values that meet both the subject and city criteria, the MATCH function will return an error. You can wrap the formula in the IFERROR function to handle this situation and return a custom message or value when no match is found. For example:
=IFERROR(INDEX(Shamal_D[Teacher_name], MATCH(MAX(IF((Shamal_D[Subject]=[@Subject])*(Shamal_D[city]=[@City]), Shamal_D[Sales Count], -1)), Shamal_D[Sales Count], 0)), "No match found")
This modified formula will display "No match found" when there is no matching teacher.
By checking these points, you should be able to identify and resolve the #N/A error in your formula.
If this does not solve your problem, then you should enter detailed information. Information about what exactly you want to do, nothing can be seen from the picture that can help. Step by step (cell by cell) description. Additional information such as Excel version, operating system, storage medium, etc. would also help.
- Thank you dear,
I will try to create a sample of the workbook and send it.
Best Regards.
