Forum Discussion
Index and match #N/A
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.
