Find cell value from Row & Column match

[nularry]

I am trying to create a formula to get a cell value that matches a specified item which is in the row, and specified item which is in the column title. Example below. Data is A1:E5. I am wanting a formula where the question marks are H2 to return a value of 8 (Banana row & 2021 column). Thank you for any help you can give.

	2020	2021	2022	2023			2021
Apple	5	7	3	2		Banana	???
Orange	2	6	2	8
Banana	9	8	5	2
Grape	6	5	9	5

 

[nularry]

I am trying to create a formula to get a cell value that matches a specified item which is in the row, and specified item which is in the column title. Example below. Data is A1:E5. I am wanting a formula where the question marks are H2 to return a value of 8 (Banana row & 2021 column). Thank you for any help you can give.

	2020	2021	2022	2023			2021
Apple	5	7	3	2		Banana	???
Orange	2	6	2	8
Banana	9	8	5	2
Grape	6	5	9	5

These are actually in different tabs.

 

[noveske]

=INDEX(B2:E5, MATCH("Banana", A2:A5, 0), MATCH(2021, B1:E1, 0))

 

[noveske]

These are actually in different tabs.

If the first is Sheet1...

Put this on the other sheet "tab".

=INDEX(Sheet1!B2:E5, MATCH("Banana", Sheet1!A2:A5, 0), MATCH(2021, Sheet1!B1:E1, 0))


That's if Apple is A2 and 2020 is B1...

 

[PeteWright]

Book1.xlsx
	A	B	C	D	E	F	G	H
1		2020	2021	2022	2023			2021
2	Apple	5	7	3	2		Banana	8
3	Orange	2	6	2	8
4	Banana	9	8	5	2
5	Grape	6	5	9	5
Sheet4

Cell Formulas
Range	Formula
H2	H2	= INDEX(B2:E5,MATCH(G2,A2:A5,0),MATCH(H1,B1:E1,0))

_______________________________________
EDIT:

= INDEX(Sheet1!B2:E5,MATCH(A2,Sheet1!A2:A5,0),MATCH(B1,Sheet1!B1:E1,0))

Just replace all occurences of "Sheet1" with your sheet name.

 

[nularry]

If the first is Sheet1...

Put this on the other sheet "tab".

=INDEX(Sheet1!B2:E5, MATCH("Banana", Sheet1!A2:A5, 0), MATCH(2021, Sheet1!B1:E1, 0))


That's if Apple is A2 and 2020 is B1...

Thank you very much for the help.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[proficient]

 

[Fluff]

Why use the volatile offset function, when there is no need for it?

 

[proficient]

Solution # 2:

=INDEX(B3:E6,MATCH(G3,A3:A6,0),MATCH(G2,B2:E2,0))

Solution # 3:

=XLOOKUP(G3,A3:A6,XLOOKUP(G2,B2:E2,B3:E6))

Solution # 4:

=SUMPRODUCT((A3:A6=G3)*(B2:E2=G2)*(B3:E6))

Solution # 5:

=VLOOKUP(G3,A2:E6,MATCH(G2,B2:E2,0)+1,0)