I'm trying to use the following formula but I get a #VALUE! error.
=INDEX(WorkbookName.xlsm! '2018:End'!$F:$F, MATCH(SheetName!$J$3, WorkbookName.xlsm! '2018:End'!$A:$A, 0))
I would like to have my data broken into multiple sheets by year. The structure of each sheet is identical. Each sheet is named for the year, and then there is a blank sheet named "End" to specify the end of the range of sheets that contain data. That way I can add a new sheet for each year (before the "End" sheet) without affecting my formula. I want to be able to search column A on every sheet within the sheet range '2018:End' for a value entered by the user (in this example, SheetName!$J$3) and return a value on the row that contains the user-entered value (in this example, the value in column F.) If it matters, I'm using named ranges that correspond to the particular columns (i.e., '2018:End'!$A:$A), but I've replaced their names with the actual ranges in the formula above. Does anyone know how to accomplish what I'm trying to do?
Bookmarks