Preface
In Google Sheets, extracting cells from matrix data is typically done by combining the VLOOKUP and MATCH functions (and more recently, by nesting the XLOOKUP function). However, it would be much easier if it could be done with a single function from the start. Yet, for some reason, despite its high frequency of use, a dedicated function for this purpose has never been provided (even in Excel and OpenOffice) as of August 2026.
Therefore, I created my own function that allows for direct extraction. (As a side note, functions like INDEX and DGET are not considered because they are very difficult to use and do not qualify as direct extraction.)
Specification
syntax:
XYLOOKUP(range, xkey, ykey[, defaultValue = null])
argument:
range The range of data including matrix headers.
xkey Search column header (horizontal header)
ykey Search row header (vertical header)
[defaultValue] Value to use if the cell is not found (optional)
Return value:
If xkey is not found, "#ErrXkey" or defaultValue.
If ykey is not found, "#ErrYkey" or defaultValue.
If both are found, cell value
How to create a script
- Select "AppsScript" from the "Extensions" of the spreadsheet you want to use this function with.
- Replace the script on the displayed screen with the following script.
- Click "Save project to Drive" near the center of the menu icon to save the project.
Script
If you don't like the function name or the order of the arguments, please adjust them yourself.
/**
* Matrix data extraction function
* @param {range} range The range of data including matrix headers
* @param {value} xkey Search column header (horizontal header)
* @param {value} ykey Search row header (vertical header)
* @param {optional} defaultValue Value to use if the cell is not found (optional)
* @return {cell} The value of the cell / defaultValue / "#ErrYkey" / "#ErrXkey"
* @customfunction
*/
function XYLOOKUP(range, xkey, ykey, defaultValue = null) {
let yindex = -1;
for (let y = 0; y < range.length; y++) { // Using map to create a one-dimensional array is a waste of memory so we don't use it
if (range[y][0] === ykey) {
yindex = y;
break;
}
}
if (yindex === -1) {
return (defaultValue != null) ? defaultValue : "#ErrYkey";
}
let xindex = range[0].indexOf(xkey); // The column data is a simple one-dimensional array, so we use indexOf.
if (xindex === -1) {
return (defaultValue != null) ? defaultValue : "#ErrXkey";
}
return range[yindex][xindex];
}
How to use the function you created
The function you create (AppsScript) is linked to the spreadsheet, so it's added to the function list as soon as you save the script and can be used immediately.
One characteristic of AppsScript is that system messages appear in cells during calculations, but the data is displayed immediately, so you can ignore them. It would be even more user-friendly if there wasn't this lag...
