Forum Discussion
Index & Match Formula Not Working
In broad terms, I'm on sheet 2 and wanting to show the contents of a cell from sheet 1.
Basically, I want whatever is in column C of sheet 1 for the row that has column A in Sheet 1 matching A2 in Sheet 2 AND also has column B in Sheet 1 matching A1 in Sheet 2. I want the contents of that cell to show in Sheet 2.
I've made this formula, but clearly I'm missing something (names of sheets have been changed, as the actual ones are people's names):
=INDEX('Sheet 1'!C:C,MATCH('Sheet 2'!A1,'Sheet 1'!B:B),MATCH('Sheet 2'!A2,'Sheet 1'!A:A))
Any help would be appreciated.
3 Replies
Hi,
Based on the example given by OliverScheurich​ you can use the following formula in Sheet2.
=INDEX(Sheet1!C:C, MATCH(A2 & A1, Sheet1!A:A & Sheet1!B:B, 0))If the Excel you are running is earlier than Excel 2021 then this formula must be applied as an array formula (i.e. using CTRL+SHIFT+ENTER from the keyboard). For Excel 2021 or newer versions it can be used as a normal formula (i.e. simply using ENTER only).
Hope this helps.
IlirU
=INDEX('Sheet 1'!C:C,MATCH(1,('Sheet 2'!A1='Sheet 1'!B:B)*('Sheet 2'!A2='Sheet 1'!A:A),0))This formula works in my sample file and in modern and legacy Excel. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2024.
Amazing. Thanks so much! I think it was me using the commas instead of equals sign.
