Google Spreadsheets function allows you to get a cell from a range by row and column name

Question

I have a range

idz01 idz04 ida02 foo a 1 b bar c 3 8 baz 8 2 g 

And I want to get values ​​from this range, for example:

 SOME_FUNCTION(rangeID, 'foo', 'idz04') 

get 1 etc. Or, ideally, get a cell:

 INDIRECT(SOME_FUNCTION(rangeID, 'foo', 'idz04')) 

or such.

Is there something like that? Is it possible to use GETPIVOTDATA() for this?

Edit: I could somehow use LOOKUP if I could get result_range :

 LOOKUP("foo", DataRange, FIRST_COL( OFFSET(DataRange, MATCH("idz04", FIRST_ROW(DataRange), 0) ) ) ) 

Only I do not have FIRST_ROW() and FIRST_COL() ... Maybe FILTER() can help?

Answer 1

Here he is:

 =INDEX(ObjednavkyData, // Find the offset of "vesela01" in the 1st column MATCH("vesela01", OFFSET((ObjednavkyData),0,0, MAX(ARRAYFORMULA(ROW(ObjednavkyData))) // Num of rows in the range , 1) ,0), // Find the offset of "z03" in the 1st row MATCH("z03", OFFSET(ObjednavkyData, 0 , 0, 1) // First row , 0)) 

Not very neat: P
Also see Google Sheets: how to get the first (Nth) row / column from a range? (built-in functions)

Answer 2

Try this, you just need to set the ranges:

 =INDEX("Talbe",MATCH("foo","First Column",0),MATCH("idz04","First Row",0)) 

For example, if your table is in the range A1: D4, use:

 =INDEX(A1:D4,MATCH("foo",A:A,0),MATCH("idz04",1:1,0)) 

You can also use named ranges

---

To get the first columns and rows, use this formula:

 =INDEX(data,MATCH("foo",INDIRECT(CHAR(64+COLUMN(data))&":"&CHAR(64+COLUMN(data))),0),MATCH("idz04",INDIRECT(COLUMN(data)&":"&COLUMN(data)),0)) 

---

To do this, using a script, use this code:

 function LOOKUPGRID(data, rowHeader, colHeader) { for (var i = 0; i<data.length;i++) { if (data[i][0] === rowHeader) { for (var j = 0; j<data[0].length;j++) { if (data[0][j] === colHeader) { return (data[i][j]); } } } } return (null) } 

You can then invoke the formula on your sheet as follows:

 =LOOKUPGRID(A1:D4,"foo","idz04") 

Answer 3

To do this, you can write a custom function using Google Apps Script. See more details.

You can access the distribution sheet with a name or identifier and get a range of values ​​(in your case a cell).

Hope this helps!

Answer 4

Tried to write my own function ...

 /** * Returns [Range] the cell value at coordinates determined by the values first row (colHeader) and first column (rowHeader) in given range. */ function LOOKUP_GRID(range, colHeader, rowHeader){ for(var col = range.getWidth()-1; col--; col >= 0 ) if(range.getCell(1, col).getValue() === colHeader) break; for(var row = range.getHeight()-1; row--; row >= 0 ) if(range.getCell(row, 1).getValue() === rowHeader) break; if(col === -1 || row === -1) return null; else return range.getCell(row, column) }