Index with match and (maybe) offset?

[Excellling]

Hi All,

I have been digging around trying to find previous answers for my question but haven't been too successful.

I am trying to do a fairly simple index match but I think I need an offset thrown in there also.

For the example data below, I would like to feed in Fruit and Orange and 2026 and have it return 97.

I seem ok with the first two but not sure how to use the row with year in my formula to return the correct column but also index/match the first two columns.

Any suggestions and to improve my understanding of formulas would be appreciated.

Thanks

 

[Cubist]

Which version of Excel are you using? Please update your profile.

 

[Excellling]

Which version of Excel are you using? Please update your profile.

Thank you Cubist, I have now updated my details.

 

[Peter_SSs]

I would like to feed in Fruit and Orange

Do you need to feed in both? Do you have any "Product" that would fit into more than one "Type" in column A?

and 2026 and have it return 97.

Can you explain how you get 97 out of Fruit, Orange and 2026 from your sample data?

 

[Excellling]

Do you need to feed in both? Do you have any "Product" that would fit into more than one "Type" in column A?


Can you explain how you get 97 out of Fruit, Orange and 2026 from your sample data?

Sorry my random numbers must have updated on cell refresh. In the case above I am looking for 50, not 97.

In terms of product and type, yes I will have Products that will fit within multiple Types.

 

[Cubist]

Note: I've created a fictitious data set below thus won't match your image.

One way with INDEX/MATCH is to join Type and Product together as a string to find the row index (see B14).
I've also provided an alternative using dynamic functionality and formula in version 365 (see B16). Option 2 will return all instances.

Book1
	A	B	C	D	E	F
1	Type	Product	2024	2025	2026	2027
2	Fruit	Apple	85	42	87	33
3	Fruit	Pear	76	40	74	15
4	Fruit	Orange	85	22	83	88
5	Vegetables	Avocado	38	45	98	80
6	Vegetables	Zucc	70	5	54	31
7	Vegetables	Potato	28	47	15	54
8	Vegetables	Squash	82	81	24	75
9	Vegetables	Carrot	77	43	100	64
10	Fruit	Orange	77	43	65	64
11
12
13	Fruit	Orange	2026
14	Option 1	83
15
16	Option 2	83
17		65
Sheet6

Cell Formulas
Range	Formula
B14	B14	=INDEX(C2:F10,XMATCH(A13&"|"&B13,A2:A10&"|"&B2:B10),XMATCH(C13,C1:F1))
B16:B17	B16	=TOCOL(IFS((A2:A10=A13)*(B2:B10=B13)*(C1:F1=C13),C2:F10),2)
Dynamic array formulas.

 

[Excellling]

Note: I've created a fictitious data set below thus won't match your image.

One way with INDEX/MATCH is to join Type and Product together as a string to find the row index (see B14).
I've also provided an alternative using dynamic functionality and formula in version 365 (see B16). Option 2 will return all instances.

Book1
	A	B	C	D	E	F
1	Type	Product	2024	2025	2026	2027
2	Fruit	Apple	85	42	87	33
3	Fruit	Pear	76	40	74	15
4	Fruit	Orange	85	22	83	88
5	Vegetables	Avocado	38	45	98	80
6	Vegetables	Zucc	70	5	54	31
7	Vegetables	Potato	28	47	15	54
8	Vegetables	Squash	82	81	24	75
9	Vegetables	Carrot	77	43	100	64
10	Fruit	Orange	77	43	65	64
11
12
13	Fruit	Orange	2026
14	Option 1	83
15
16	Option 2	83
17		65
Sheet6

Cell Formulas
Range	Formula
B14	B14	=INDEX(C2:F10,XMATCH(A13&"|"&B13,A2:A10&"|"&B2:B10),XMATCH(C13,C1:F1))
B16:B17	B16	=TOCOL(IFS((A2:A10=A13)*(B2:B10=B13)*(C1:F1=C13),C2:F10),2)
Dynamic array formulas.

Thank you Cubist for the above.

The second option seems to work well, though it does seem to have an issue as it works for once cell but when I drag it I get a message saying 'Excel ran out of resources while attempting to calculate one or more formulas'. Not sure whether the formula is resource intensive?

This workbook hasn't had that error before.

 

[Cubist]

The second option seems to work well, though it does seem to have an issue as it works for once cell but when I drag it I get a message saying 'Excel ran out of resources while attempting to calculate one or more formulas'. Not sure whether the formula is resource intensive?

How large of a dataset are you working with?

Is it throwing the error with the INDEX/MATCH?

 

[Peter_SSs]

I am assuming that the combinations in columns A & B will not repeat

24 11 18.xlsm
	A	B	C	D	E	F
1	Type	Product	2024	2025	2026	2027
2	Fruit	Apple	85	42	87	33
3	Fruit	Pear	76	40	74	15
4	Fruit	Orange	85	22	83	88
5	Vegetables	Avocado	38	45	98	80
6	Vegetables	Zucc	70	5	54	31
7	Vegetables	Potato	28	47	15	54
8	Vegetables	Squash	82	81	24	75
9	Vegetables	Carrot	77	43	100	64
10	Fruit	Pineapple	77	43	65	64
11
12
13	Fruit	Orange	2026	83
14	Vegetables	Potato	2024	28
15	Fruit	Apple	2027	33
16	Vegetables	Pineapple	2024	not found
17	Fruit	Apple	2021	not found
Excelling

Cell Formulas
Range	Formula
D13:D17	D13	=IFERROR(FILTER(FILTER(C$2:F$10,C$1:F$1=C13),(A$2:A$10=A13)*(B$2:B$10=B13)),"not found")

 

[Excellling]

I am assuming that the combinations in columns A & B will not repeat

24 11 18.xlsm
	A	B	C	D	E	F
1	Type	Product	2024	2025	2026	2027
2	Fruit	Apple	85	42	87	33
3	Fruit	Pear	76	40	74	15
4	Fruit	Orange	85	22	83	88
5	Vegetables	Avocado	38	45	98	80
6	Vegetables	Zucc	70	5	54	31
7	Vegetables	Potato	28	47	15	54
8	Vegetables	Squash	82	81	24	75
9	Vegetables	Carrot	77	43	100	64
10	Fruit	Pineapple	77	43	65	64
11
12
13	Fruit	Orange	2026	83
14	Vegetables	Potato	2024	28
15	Fruit	Apple	2027	33
16	Vegetables	Pineapple	2024	not found
17	Fruit	Apple	2021	not found
Excelling

Cell Formulas
Range	Formula
D13:D17	D13	=IFERROR(FILTER(FILTER(C$2:F$10,C$1:F$1=C13),(A$2:A$10=A13)*(B$2:B$10=B13)),"not found")

Thank you both, both options are working for me.

Cubist - option 1 with index/match
Peter - filter option also works, for me I haven't used filter in a formula before so good for my learning.

I appreciate your time and assistance