Referencing last row in Excel formula

0
votes

In a formula, how can I refer to the last row of a column?

In Sheet1, I have a formula:

=IF(ISNA(VLOOKUP(A2, Sheet2!A$2:A????, 1, FALSE)), "NO", "YES")

In Sheet2, I have numbers in column A. However, I don't want to hard code the row number of the last row in the above formula because the number of rows keeps changing.

Is there something I can substitute for "????" ? I'm not looking for a VBA/macro way of doing it.

I'll be applying this formula to several thousand rows in Sheet1. So, it would also be nice to know if I can save the last row's number into say Sheet2!B1 and then use B1 in place of "????".

2
excelexcel-formula

2 Answers

2
votes

You can use following one:

=IF(ISNA(VLOOKUP(A2, INDIRECT("Sheet2!A$2:A" & MATCH(9E+307,Sheet2!A:A)), 1, FALSE)), "NO", "YES")

but since INDIRECT is volatile function this solution is slow.

Better one is:

=IF(ISNA(VLOOKUP(A2, Sheet2!A:A, 1, FALSE)), "NO", "YES")
1
votes

Another option is to use COUNTIF - that function can reference the whole column but will only usually look at the "used range" so there shouldn't be any significant overhead, i.e.

=IF(COUNTIF(Sheet2!A:A,A2),"YES","NO")