Search code examples
excelexcel-formula

How do I get the fine amount in this table which determined by 2 factors with reference?

I'm trying to get the value for the fine amount in this table on the left in which determined by 2 factors: no. of days & no. of offences with reference of the fine amount at the side. However I can't really get the value no matter how I trial and error with possible functions and ended up with errors which I open up and see which part of the error is in and it happens on the part where comparing the no. of days with the reference (which I putting the value from the table to be greater than or equals to the first number in the reference). table & ref

As the reference at the side in which the no. of days is shown in strings, I got the min number of days out so as I can easily compare with the value in the table. I've tried using INDEX MATCH, Filter, Vlookup to get the fine amount but failed at the it and got to a point that I'm kind of overwhelmed because the logics seems correct but it just doesn't give me the results. The last I tried was: =FILTER(M2:N6,ISNUMBER(MATCH(G2,G2>=K2:K6,0))*ISNUMBER(MATCH(H2,M1:N1,-1)))

Appreciate if anyone could correct/help me out with this.

Solution

  • Try using one of the following formulas:

    enter image description here

    =LOOKUP([@[NO. OF DAYS]],--TEXTBEFORE(L$2:L$6," "),IF(H2=0,M$2:M$6,N$2:N$6))

    Or, Using XLOOKUP()

    =XLOOKUP([@[NO. OF DAYS]],--TEXTBEFORE(L$2:L$6," "),IF(H2=0,M$2:M$6,N$2:N$6),,-1)

    Or, Using INDEX()+MATCH()

    =INDEX(IF(H2=0,M$2:M$6,N$2:N$6),
 MATCH([@[NO. OF DAYS]],--TEXTBEFORE(L$2:L$6," "),1))

    Using TEXTSPLIT() within LOOKUP() seems to bit shorter:

    =LOOKUP([@[NO. OF DAYS]],--TEXTSPLIT(L$2:L$6," "),IF(H2,N$2:N$6,M$2:M$6))