Two-way lookup with intervals, return closest HIGHER value from header row.

[JollyJumper]

Hello forum, I'm new here and have never posted before. I can't seem to figure this out and have come here for help.

This is my current situation: I have a table ranging from A1 to L9 in which I need a two-way lookup with Index and Match that will return a value from the horizontal first row (the bold numbers ranging from 20 to 300), based on the matching row and it's matching value.

So an example: say the input that is supposed to match (this is always an exact match) the vertical first column A is 3,5 and the value of the horizontal input is 11 then the function should return 50, since 3,5 and 11 matches 50 from the header row.

Now my problem is that if the horizontal input value is between the intervals it won't return the closest higher value.

For example: If the input for the vertical column is 3,5 and the horizontal value is 25 it SHOULD return 100 from the header row because it is between 22 and 32. It should always return the closest HIGHER number from the header row if it's bigger than the lower.

To fix this I tried to make an IFERROR function, and it works. If the input is 3,5 and 25 it returns 100 from the header row, and so on. But now they need to work together.

Function that checks for a match:
=INDEX(B1:L1;MATCH(N2;INDEX($B$2:$L$9;MATCH(N1;A2:A9;0);0))
From example 1: N1 is the cell for input where 3,5 for the vertical column is, and N2 is the horizontal input value, 11 in the first example.

Function that fixes the interval problem:
=IFERROR(SMALL(IF($B$2:$L$2>=N3;$B$1:$L$1);1);"Try again!")
N3 is just where the test input is, for the horizontal values. This is tested on the second row (B2-L2).

So my question is: Can I combine these two and if so, how? Is there an easier solution for this problem maybe?
Note: Both of the functions I have work on their own, but I can't seem to get them to work together.
Thank you so much in advance!

	20	30	50	75	100	125	150	175	200	250	300
3,5	1	4	11	22	32	42	52	62	73	93	113
5	1	3	7	14	22	29	36	43	50	64	79
7	1	3	5	10	15	20	25	30	35	45	56
10		1	3	7	10	14	17	21	25	32	39
14			2	4	7	9	12	14	17	22	27
20			1	2	4	6	8	9	11	15	18
28						4	5	6	7	10	12
40							3	4	5	6	8

<tbody>
</tbody>

 

[pgc01]

Hi
Welcome to the board

With your input values in N1 and N2, like you posted, try this array formula:

=INDEX($B$1:$L$1,MATCH(TRUE,INDEX($B$2:$L$9,MATCH(N1,$A$2:$A$9,0),0)>=N2,0))

... confirmed with CSE

 

[JollyJumper]

Hi
Welcome to the board

With your input values in N1 and N2, like you posted, try this array formula:

=INDEX($B$1:$L$1,MATCH(TRUE,INDEX($B$2:$L$9,MATCH(N1,$A$2:$A$9,0),0)>=N2,0))

... confirmed with CSE

This works! Thanks a bunch mate, you have no idea how long I've been trying to fix this, heh..

Really grateful for your help.

 

[pgc01]

You're welcome.