CODEWITHSUNDEEP
excelformulavlookup
user2562455
user2562455

Reputation: 33

understanding and excel formula I have been using

I have been using the following formula to compare strings and show all the matches. It works perfectly but I am trying to increase my overall understanding. 

=IF(ISNA(VLOOKUP($B8,N$1:N$1048576,1,0)),"",1)

From what I know this will look up all strings between N1 - N104 and compare them to the string located in B* and return a 1 if found and a 0 if not found what is confusing me is the -- 8576 number what does it do ?

Upvotes: 0

Views: 516

Answers (2)

oboewan42
oboewan42

Reputation: 88

1048576 (2 to the 20th power) is the maximum number of rows in a worksheet in Excel 2007 and later. (In previous versions, it was 65536, or 2 to the 16th power.)

Basically, the N$1:N$1048576 refers to "all the cells in column N".

However, for safety reasons, you should change that part of the formula to the simpler N:N - in fact, if I copy your formula, click on an Excel cell, and press Ctrl+V, Excel does that replacement automatically.

Upvotes: 4

Charles Caldwell
Charles Caldwell

Reputation: 17169

As stated in an article on Office.com, the maximum size of an Excel spreadsheet is:

1,048,576 rows by 16,384 columns

That's where your "8576" is coming from. Your formula is not checking from cell N1:N104 but rather the entire column of N.

Another way of writing your formula would be:

=IF(ISNA(VLOOKUP($B8,N:N,1,0)),"",1)

Upvotes: 2

Related Questions