How can I retrieve a specific cell value from a matrix in Google Sheets?

You can solve this by “finding the intersection” of the row for Detached and the column for 4 Bed. Two common approaches are described below:

---

1. Using a Built‐In Sheets Formula

If your data is arranged so that one row shows the column headers (e.g. the bed types) and one column shows the property types (like Detached, Semi-detached, etc.), you can combine the INDEX and MATCH functions. For example, suppose:

• The property types are in cells A2:A6.
• The bed types are in cells B1:F1.
• The matrix of values is in cells B2:F6.

Then the following formula retrieves the value at the intersection of "Detached" and "4 Bed":

PLAINTEXTCopied
=INDEX(B2:F6, MATCH("Detached", A2:A6, 0), MATCH("4 Bed", B1:F1, 0))

This works as follows:

• The first MATCH finds the row index for "Detached".
• The second MATCH finds the column index for "4 Bed".
• INDEX then retrieves the value at that intersection—in your example, this should be 8.

---

2. Retrieving the Value Using the Sheets API

If you need to work programmatically (for example, using the Google Sheets API) you can do one of the following:

a. Retrieve the Entire Range and Process on the Client

Use the spreadsheets.values.get method to fetch the range that contains your matrix. For example:

JSCopied
const {google} = require('googleapis');

async function getMatrixRange(spreadsheetId, range) {
  const sheets = google.sheets('v4');
  const result = await sheets.spreadsheets.values.get({
    spreadsheetId,
    range,  // e.g. "Sheet1!A1:F6"
  });
  return result.data.values;
}

Then, in your code, use array methods to find the row corresponding to "Detached" (from the first column) and the column corresponding to "4 Bed" (from the header row). For instance:

JSCopied
async function findIntersectionValue(spreadsheetId) {
  const matrix = await getMatrixRange(spreadsheetId, "Sheet1!A1:F6");

  // Assuming the first row contains bed types and first column contains property types.
  const bedTypes = matrix[0];
  let targetCol = bedTypes.indexOf("4 Bed");
  
  // Find the row for "Detached" (skip header row)
  let targetRow;
  for (let i = 1; i < matrix.length; i++) {
    if (matrix[i][0] === "Detached") {
      targetRow = i;
      break;
    }
  }
  
  // Retrieve the value (adjust index by 1 since first column is property types)
  const value = matrix[targetRow][targetCol];
  return value;
}

b. Retrieve a Specific Cell (Using Query Parameters)

If you know exactly where the value lives (say, cell D4) you can also use the cell feed method (available in the legacy API) or specify the exact range with the Sheets API. For example, using the API’s spreadsheets.values.get with A1 notation:

JSCopied
const result = await sheets.spreadsheets.values.get({
  spreadsheetId,
  range: "Sheet1!D4",
});
console.log(result.data.values[0][0]);  // should output 8

---

Both methods achieve the goal. The built‐in formula method is ideal for a one-time lookup inside Sheets, whereas the API methods give you flexibility if you need to process the data programmatically.