MATCH function with Greater than (and ascending order)

[DashboardNovice]

In the attached file, I am getting a value of 4 for the MATCH function, which is returning he value in Column G. However, I would like to return a value of 5 which corresponds to column H.

Row 6 is my MATCH array, but this would work ask if I have the age range on row 4.

So for age 43, in cell D10, I should return $36,960 but instead I get $112,586. I tried adding one to the MATCH function, but this doesn't work if I use a number on the high end of any given column, e.g. if I use 40, instead of getting $112,586 I get $36,960. So adding one to the MATCH function doesn't work.

I tried using "-1" as the last argument and I get the #N/A error.

Any help would be greatly appreciated.

 

[tomas]

Hi

You can modify formula like this

=INDEX(D7:L7,1,MATCH(D10+4.5,$D$6:$L$6))

Adding 4.5 will do the job.

-1 should work if lookup values are in descending order so exactly opposite how you have it.

 

[NARAYANK991]

Hi ,

Why not use a simple HLOOKUP ? See the attached file and comment.

Narayan

 

[Somendra Misra]

Hi,

If you just change the values of Age array to lower values like 0,25,31,36... and so on you can use below formula also.

=LOOKUP(D10,$D$6:$L$7)

Regards,