I am using LOOKUP function to fetch values from a remote table. It works fine if the beginning and end cells are explicitly defined. However, the external list has daily currency exchange values and the list is populated every day. It is a needy work to edit the last cell number every time, I wonder if there is a way to automatically recognize the last non-zero value in the column and span until that.
The lookup function looks like this:
LOOKUP(AK77+1;'file:///home/TCMB/EVDS.xlsx'#$EVDS.$A$2:$A$237;IF(D77="EUR";'file:///home/TCMB/EVDS.xlsx'#$EVDS.$C$2:$C$237;'file:///home/TCMB/EVDS.xlsx'#$EVDS.$B$2:$B$237))
EVDS.xls is the exchange currency database file. A column is the date, B column is the USD and C column is the EUR values. AK77 cell contains the date to search in the exchange currency database file. According to desired EUR or USD option in D77 proper exchange value is fetched from the database. 237 is the last non-zero element to this date in the database, and this is the one needs to be updated regularly by hand. Unfortunately due to the nature of LOOKUP function, giving a number much higher such as 999 results with an error, therefore exact number containing non-zero elements should be entered. In order to find this last cell containing a non-zero element i came across this topic:
Return the last value in a column
and I can find the last element in the EVDS.xls file with this command:
ADDRESS(MATCH(2;1/ISNUMBER(A1:A999);A1:A999);1)
However, this command works only in the remote file, and I can not embed it into the LOOKUP function. Is there a proper way to do this? Any help is appreciated.