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 S...
HelloDarkMoose,
Your formula needs to match both column A and column B together. Try this version:
=INDEX(‘Sheet 1’!C:C, MATCH(1, (‘Sheet 1’!A:A=‘Sheet 2’!A2) * (‘Sheet 1’!B:B=‘Sheet 2’!A1), 0))
This checks column A against A2 in Sheet 2
Checks column B against A1 in Sheet 2
Only rows where both conditions are true return 1
MATCH finds that row, and INDEX returns the value from column C
In Excel 365/2021, just press Enter. In older versions, confirm with Ctrl+Shift+Enter.
If you have XLOOKUP available, here’s an even simpler alternative:
=XLOOKUP(1, (‘Sheet 1’!A:A=‘Sheet 2’!A2) * (‘Sheet 1’!B:B=‘Sheet 2’!A1), ‘Sheet 1’!C:C)
Both formulas will return the value from column C in Sheet 1 where column A matches A2 and column B matches A1 in Sheet 2.