Index Match Two-Way Lookup Help

[sole32]

Hello,

I am trying to do a two-way lookup of a matrix table using index match given two things:

1. Year
2. Value

Since the table is basically just a range of values per year, I also need to apply closest match to determine which is the best category to assign to the provided value.

See below for a sample of the table:

Row 1 contains the years going back to 1998.
Beneath the year, are the values themselves.

And Column A contains the category (1-16) that I need to return.

	2024	2023	2022				2000	1999	1998
1	100	99	95				55	51	48
2	99	97	90				52	49	44
3	96	93	86				50	47	39
14	87	85	68				22	20	16
15	83	81	60				19	18	15
16	80	78	56				16	15	12

As mentioned above, I am given Year and Value.
Need to return the category (Col A) that is closest based on the table.

Example:

Year	Provided Value	Category
2023	91	Should return 3 here
2022	87	Should return 3 here
1998	15	Should return 14 here

This would work perfectly if I only had one year of data to lookup (i.e. 2023):
=index(Table!$A$2:$A$17,match(min(abs(Table!$C$2:$C$17-$C3)),abs(Table!$C$2:$C$17-$C3),0)))

Unfortunately my dataset spans all 25+ years.

Any thoughts on how to do this using index match?
Feel like I'm kind of on the right track with the above formula, but struggling right now to incorporate the two-way lookup.

 

[Peter_SSs]

Welcome to the MrExcel board!

Shouldn't the last result be 15?

Anyway, see if this helps. You will have to adjust the ranges/sheet names to suit your actual layout.

23 08 26.xlsm
	A	B	C	D	E	F	G	H	I	J
1		2024	2023	2022				2000	1999	1998
2	1	100	99	95				55	51	48
3	2	99	97	90				52	49	44
4	3	96	93	86				50	47	39
5
6
7
8	14	87	85	68				22	20	16
9	15	83	81	60				19	18	15
10	16	80	78	56				16	15	12
11
12
13	Year	Value	Cat
14	2023	91	3
15	2022	87	3
16	1998	15	15
sole32

Cell Formulas
Range	Formula
C14:C16	C14	=LET(y,ABS(FILTER(B$2:J$10,B$1:J$1=A14)-B14),INDEX(A$2:A$10,MATCH(MIN(y),y,0)))

Also, one further question: What should happen if two categories are equal closest match? For example: Year = 2022, Value = 88
In this case my formula above would return Category 2, but Category 3 is equally close.

 

[sole32]

Thanks for the reply.
And yes good catch! Last line in the example should return category 15.

Also, thanks for bringing up that use case.
For closest match where the value directly in-between two values, I would need to round down and return the category of the lower value.

What would need to be changed in the formula as a result?

 

[Peter_SSs]

For closest match where the value directly in-between two values, I would need to round down and return the category of the lower value.

What would need to be changed in the formula as a result?

Is the column D formula what you mean?

23 08 26.xlsm
	A	B	C	D	E	F	G	H	I	J
1		2024	2023	2022				2000	1999	1998
2	1	100	99	95				55	51	48
3	2	99	97	90				52	49	44
4	3	96	93	86				50	47	39
5
6
7
8	14	87	85	68				22	20	16
9	15	83	81	60				19	18	15
10	16	80	78	56				16	15	12
11
12
13	Year	Value	Cat	Cat
14	2023	91	3	3
15	2022	87	3	3
16	1998	15	15	15
17	2022	88	2	3
sole32

Cell Formulas
Range	Formula
C14:C17	C14	=LET(y,ABS(FILTER(B$2:J$10,B$1:J$1=A14)-B14),INDEX(A$2:A$10,MATCH(MIN(y),y,0)))
D14:D17	D14	=LET(y,ABS(FILTER(B$2:J$10,B$1:J$1=A14)-B14),INDEX(A$2:A$10,XMATCH(MIN(y),y,,-1)))

 

[sole32]

Thanks @Peter_SSs!

Both formulas appear to be working as intended (both in rounding up and down).
I'll do a more rigorous check tomorrow - but looks good to me.

 

[Peter_SSs]

Cheers. Hope it works for you.

 

[sole32]

Sorry for digging this up again.
Previously, I was looking to get exact match or closest match in the range.
And for instances where the number fell directly between two values, then round down.

But the ask has changed somewhat.
Now, I am in need of exact match or if no exact match is found, then round down to the nearest value (regardless of how close another higher value may be).

So if this is the example data set:

	2024	2023	2022				2000	1999	1998
1	100	99	95				55	51	48
2	99	97	90				52	49	44
3	96	93	86				50	47	39
14	87	85	68				22	20	16
15	83	81	60				19	18	15
16	80	78	56				16	15	12

And this is the data given:

Year	Provided Value	Category
2023	91	Should return 14 here (Need to round down even though #3 is closer)
2022	88	Should return 3 here (Round down)
1998	15	Should return 15 here (due to exact match)

 

[sole32]

What would be the best way to handle this?

Is changing the previous index match viable?
Since the logic was updated, it seems that an amendment(s) would need to be made.

 

[Peter_SSs]

Sorry, I have been away for a couple of weeks. Is this what you mean?

23 10 04.xlsm
	A	B	C	D	E	F	G
1		2024	2023	2022	2000	1999	1998
2	1	100	99	95	55	51	48
3	2	99	97	90	52	49	44
4	3	96	93	86	50	47	39
5	14	87	85	68	22	20	16
6	15	83	81	60	19	18	15
7	16	80	78	56	16	15	12
8
9	Year	Provided Value	Category
10	2023	91	14
11	2022	88	3
12	1998	15	15
sole32 (2)

Cell Formulas
Range	Formula
C10:C12	C10	=XLOOKUP(B10,FILTER(B$2:G$7,B$1:G$1=A10),A$2:A$7,,-1)