Array Formula from Google Sheets to Excel INDEX MATCH MATCH

0
votes

this topic has been discussed in some formats, but not specifically this one. I have used an array formula in Google Sheets that works, but does not work when exported to Excel. Can you help me modify the formula so that it works in Excel?

=ArrayFormula(index('Technical Lookup Tables'!J14:L17, MATCH(C39&C40,'Technical Lookup Tables'!H14:H17&'Technical Lookup Tables'!I14:I17,0)))

This is what the reference table looks like:

enter image description here

And this is the output:

enter image description here

1
arraysexcelindexingspreadsheet
Remove the ArrayFormula() wrapper and enter the rest of the formula with Ctrl-Shift-Enter instead of Enter when exiting edit mode. - Scott Craner

1 Answers

0
votes

This formula can be written in Excel as a non-array formula:

=index('Technical Lookup Tables'!J14:L17, MATCH(C39&C40,index('Technical Lookup Tables'!H14:H17&'Technical Lookup Tables'!I14:I17,0),0))