There is a web application generating output into an excel file based on the filters users select. Therefore, the number of rows in the output file may differ. I can edit the excel template but the application code.
The excel file has two different tabs, in the second tab I need to use a VLOOKUP to retrieve some values from the first tab. After the file is generated, I use the function below to retrieve those values, it works fine:
IFERROR(VLOOKUP(B3,'Budget Analysis - P1'!$B$3:$G$500,3,FALSE),"")
I want to add this function to the excel template, the problem is I don't know how many rows there will be, so I can't directly use something like 500. When I add the function like below in the template, it works.
IFERROR(VLOOKUP(B3,'Budget Analysis - P1'!$B$3:$G$1000000,3,FALSE),"")
However, I am not happy of using G1000000 in the data range, looking for more professional and elegant solution. I can find the number of rows in the excel using the formula below:
=MIN(ROW(data))+ROWS(data)-1
What I need is something like that:
=IFERROR(VLOOKUP(B3,'Budget Analysis - P1'!$B$3:$G${=MIN(ROW(data))+ROWS(data)-1},3,FALSE),"")
I couldn't find the correct syntax or logic for that. I appreciate if you may help.
Regards,
VLOOKUP is optimised an can use full column references:
IFERROR(VLOOKUP(B3,'Budget Analysis - P1'!$B:$G,3,FALSE),"")
But if that is not acceptable and you want to limit it for some reason we can replace:
'Budget Analysis - P1'!$B$3:$G$500
With
'Budget Analysis - P1'!$B$3:INDEX('Budget Analysis - P1'!$G:$G,MIN(ROW(data))+ROWS(data)-1)
so:
IFERROR(VLOOKUP(B3,'Budget Analysis - P1'!$B$3:INDEX('Budget Analysis - P1'!$G:$G,MIN(ROW(data))+ROWS(data)-1),3,FALSE),"")
As you can see that becomes very unreadable and most likely will not save any time in the calculations.