i have to select a cell based on a data on the columns and one on the rows

Question

i have a table where in the columns there is one data like column1 = 100 ; columns2 = 200 etc..... on the rows there is some other data like rows1 = a ; rows2=b etc i need to find a " formula " where when i put in the " criteria" the column and rows data the return is the cell where the column and row meet thanks in advance

Answer 1

Hi @Luca Gallarate

Could you please share us with a simple sample for more details? I have some questions:

How did you put the "criteria"?

Does the "criteria" of column and rows data mean the data of cells in a certain row and column, or just the row number and column number?

Are there duplicate values ​​in row data and column data?

Generally, we can use Index and Match function to find the cell value at their intersection in the table based on the given column number and row number.

I created a simple sample as following image:

• We can use the formula =INDEX($A$1:$D$3, MATCH(INDEX(A:A,MIN(IF($B$1:$D$3=F2,ROW($A$1:$A$3),""))), $A$1:$A$3, 0), MATCH(INDEX($1:$1,MIN(IF($A$1:$D$3=G2,COLUMN(A:D),""))), $A$1:$D$1, 0)) to get the cell value.
• Among them, this part of the formula INDEX(A:A,MIN(IF($B$1:$D$3=F2,ROW($A$1:$A$3),""))) is used to obtain the data in the first column corresponding to the number of row where the data is located. In the first column, the data value with the row number for the data "200" is "a".
• The part of the formula INDEX($1:$1,MIN(IF($A$1:$D$3=G2,COLUMN(A:D),""))) is used to obtain the data in the first row corresponding to the number of column where the data is located. In the first row, the data value with the column number for the data "701" is "301".
• Then we get the row number and column of the value respectively to query the data.

Any misunderstandings, welcome to post back.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

An example would certainly help clarify your intentions. Are you describing an array where the top row of the array and left column are headings and you want the value of an interior cell where a pair of headings intersect. If so, consider the following example:

• Top row headings in D20:H20
• Left column headings in C21:C24
• Interior values in D21:H24
• =MATCH(x1,C21:C24,0) will return the relative row number with header value x1
• =MATCH(x2,D20:H20,0) will return the relative column number with header value x2
• =OFFSET(C20,MATCH(x1,C21:C24,0),MATCH(x2,D20:H20,0)) will return the value in the cell the intersection of the x1 row and the x2 column. x1 and x2 can be hard-coded values or references to single cells containing the appropriate values.