EXCEL - Return cell address of a variable date using INDEX MATCH

1
votes

In this example, there are two worksheets, Sheet1 and Sheet2. Sheet1 contains our formula and also contains the cell lookup value in Cell B4. Sheet2 contains the data index to be searched. The goal is to use an INDEX MATCH formula on Sheet1 to match the contents of Sheet1 Cell B4 (which in this example contains a date, '5/23/2016') against a large index that spans multiple columns (L:DX), in which dates live. The cell lookup value '5/23/2016' will only appear once on Sheet2.

I have attempted a number of unsuccessful variations of INDEX MATCH following the general syntax of:

=INDEX(Sheet2!L:DX,MATCH(Sheet1!B4,Sheet2!L:DX,0))

Sheet2!L:DX is the index to search for a cell lookup value in. Note how it spans multiple columns.

B4 is the cell lookup value

"0" specifies the Match must be Exact

I believe there must be something I am missing however, as this will return a #N/A error. I understand one cause of this error may be that MATCH is unable to search for this cell lookup value across multiple columns in the way I have it written. Wondering how this could be achieved, as I was unable to locate an example of this formula applied to multiple columns

1
excelexcel-formula
You are going to need an array formula to do this. Can we tighten up the rows to search? Arrays do not like full column references. – Scott Craner
Certainly, could for example use "L10:DX999" in lieu of "L:DX", if I'm understanding correctly. The reason I use such a large range is because the data contained in sheet2 may vary, but will always start in column L and continue across multiple columns, and will always end prior to column DX. – Majora

1 Answers

1
votes

To get the Address we can use the ADDRESS() function. To use that we need the column Number and Row number:

To find the Row:

=AGGREGATE(15,6,ROW(Sheet2!$L$10:$DX$999)/(Sheet2!$L$10:$DX$999=$B$4),1)

To find the Column:

=AGGREGATE(15,6,COLUMN(Sheet2!$L$10:$DX$999)/(Sheet2!$L$10:$DX$999=$B$4),1)

Then to find the address:

=ADDRESS(AGGREGATE(15,6,ROW(Sheet2!$L$10:$DX$999)/(Sheet2!$L$10:$DX$999=$B$4),1),AGGREGATE(15,6,COLUMN(Sheet2!$L$10:$DX$999)/(Sheet2!$L$10:$DX$999=$B$4),1),1,1,"Sheet2")