Is there a Google Sheets (or Excel) formula like HLOOKUP() but that will search over a two-dimension-CodePudding

I have a Google Sheets spreadsheet and I am hoping to write a formula that finds the location of a given phrase anywhere in the spreadsheet and then returns the value of the cell a certain number of cells below the searched-for cell. For example, if I am searching for the value "11/15/2022", and that cell is C4, I would want to return the value of cell C6. I have tried using HLOOKUP(), but that limits my search range to a single row, and I need to be able to search anywhere in the spreadsheet (and the data has dimensions that are both greater than one).

Is there a function (either Excel or Google Sheets) that will perform this? Any help is much appreciated!

CodePudding user response：

try:

=INDEX(TEXTJOIN(, 1, IF(B1:F10=A1, B3:F12, )))

CodePudding user response：

Provide this code with your lookup value, row-offset and col-offset values in H1:H3,

lookup results will be shown in column K.

If there are more than one match with the lookup value, all results will be spill out into column K. (e.g, if there is 3 matches with the given lookup value, but after applying the offsets, there are more than 1 results value, all the unique results will be returned as an array in column K).

=LAMBDA(LOOKUPVALUE,ROWOFFSET,COLOFFSET,DATA,
 LAMBDA(ROWA,COLA,
  LAMBDA(COLLEN,ROWLEN,
   UNIQUE(FLATTEN(MAKEARRAY(ROWLEN,COLLEN,LAMBDA(ROW,COL,
    LAMBDA(CELLVALUE,
     IF(CELLVALUE=LOOKUPVALUE,INDEX(DATA,ROW ROWOFFSET,COL COLOFFSET),"")
    )(INDEX(DATA,ROW,COL))
   ))),FALSE,TRUE)
  )(COUNTBLANK(ROWA) COUNTA(ROWA),COUNTBLANK(COLA) COUNTA(COLA))
 )(INDEX(DATA,1),INDEX(DATA,,1))
)($H$1,$H$2,$H$3,$A$1:$F)

CodePudding user response：

Using the sample data provided by @Ping's answer. In cel H2, you can put the following formula:

=LET(rng, A1:E5, nRows, ROWS(rng), nCols, COLUMNS(rng), lookup, G1,
  rOffset, G2, cOffSet, G3, check, IF(rng=lookup,1,0), NF, "Not Found",
  idx, MAKEARRAY(nRows, nCols, LAMBDA(r,c, r&","&c)),
  IF(MAX(check) = 0, NF, LET(found, TEXTSPLIT(TEXTJOIN(";",,MAP(idx,check,
    LAMBDA(a,b, IF(b=1, a,"")))),",",";"),
    MAP(INDEX(found,,1), INDEX(found,,2),LAMBDA(row,col, 
      IFERROR(INDEX(rng, row   rOffset, col   cOffSet), NF)))
  ))
)

and here is the corresponding output:

check name is a [0,1] array of the same shape as rng, that is set to 1 if the lookup value was found, otherwise 0.

idx name is an array of the same shape as rng, and on each cell has row and column index position delimited by comma (,). For example row 3 and column 4 is represented as: 3,4.

Note: You cannot store the information without delimiting them by comma, because, in the case of more than one digit per row or column, you cannot differentiate the row part from the column or vice-versa.

The name found:

TEXTSPLIT(TEXTJOIN(";",,MAP(idx,check, LAMBDA(a,b, IF(b=1, a,"")))),",",";")

has on the first column all matching rows and on second column the corresponding column. The MAP function does the magic to find idx values that correspond to the lookup values found. Keep in mind that idx and check both have the same shape, that is why we can use MAP here. TEXTJOIN and TEXTSPLIT are used just to put the information in the expected format.

Finally the last MAP function is used to find the corresponding values in rng considering both column and row offset. If more than one value was found, it returns an array with the corresponding values. If you want to put the output in a single cell, you can encapsulate the result via TEXTJOIN, as follow: TEXTJOIN(",",,mapResult) where mapResult is the output of the last MAP.

This approach considers the following non happy path scenarios. It returns Not Found (it can customized to a different value):

The lookup value was not found
The offset values produce a value out of the input range