Index + Match, returning incorrect value, date match required

[JackDanIce]

Hi,

I have a table as follows:

Date Source Rate
06/04/1990 Property 18%
06/04/1990 Securities 10%
06/04/2016 Property 11%
06/04/2016 Securities 8%

A client provides values of date and source and I need to return the appropriate rate where client date is >= nearest date match in table, against the specific source

If A2 has a value of 30/03/2018, then this returns 4:

=MATCH(A2,$K$12:$K$16,1)

but source is "Property", so need to return 3, but unsure how

I've tried to use VLOOKUP as:

=VLOOKUP(A2&B2,$J$12,$K$16,2,1)

Where J12:J16 is a key of <date>&<source> but in this example it returns 18% instead of 11%, despite A2 being closer to 06/04/2016 than 06/04/1990

Any ideas how to return the correct rate based on input of date and source?

TIA,
Jack</date>

 

[JackDanIce]

lol I meant thank you for a non CSE formula (rather than avoidance of arrays!) and will post back if any troubles with updated formula, I should be able to figure out an IF clause or something to insert to return from the correct table.

Enjoy your evening,
Jack

 

[JackDanIce]

Brief update, formula now changed to consider two different columns M or N:

=IF(LOWER(B3)="higher",INDEX($N$13:$N$16,AGGREGATE(14,6,(ROW($N$13:$N$16)-ROW(N13)+1)/(($L$13:$L$16=G3)*($K$13:$K$16<=E3)),1)),INDEX($M$13:$M$16,AGGREGATE(14,6,(ROW($M$13:$M$16)-ROW(M13)+1)/(($L$13:$L$16=G3)*($K$13:$K$16<=E3)),1)))

Where the two columns are either Basic or Higher rate columns

I was thinking of using OFFSET($M$13:$M$16,,--(LOWER(B3="higher")) to avoid IF.. going to test now

Edit, it works

=INDEX(OFFSET($M$13:$M$16,,--(LOWER(B3)="higher")),AGGREGATE(14,6,(ROW($M$13:$M$16)-ROW(M13)+1)/(($L$13:$L$16=G3)*($K$13:$K$16<=E3)),1))

Inputs
Date{06/APR/2017, 30/APR/2003}
Category{Basic, Higher}
Source{Property, Securities}

Rotating through all combinations of these inputs, correctly outputs the right row/column value.

Much appreciated helping get this far.

 

[Peter_SSs]

=IF(LOWER(B3)="higher",...

FYI, this should work just as well & save one function call. Text comparisons in worksheet formulas are not case-sensitive.

IF(B3="higher",...

 

[JackDanIce]

Oh nice, didn't know that, one to remove! I'm using Boolean converts 1 or 0 to offset the column or not

Guess it's using Lcase in VBA to force case tests that made me do that in the sheet function

Current formula:

=INDEX(OFFSET($M$13:$M$16,,--(B3="higher")),AGGREGATE(14,6,(ROW($M$13:$M$16)-ROW(M13)+1)/(($L$13:$L$16=G3)*($K$13:$K$16<=E3)),1))