Index & Match Formula Not Working

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.

Maybe it was just the 0 missing from the MATCH to required exact matches.

With 365 I would use array formulas and truncate entire column references to conform to the actual data.

= INDEX(returnArray, XMATCH(TRUE, BYROW(lookupArrays=lookupValues, AND)))

or

= XLOOKUP(TRUE, BYROW(lookupArrays=lookupValues, AND), returnArray)

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.