Excel Formulas and Tutorial

IF, ISNA & VLOOKUP FUNCTIONS

How to handle more than 65536 rows in EXCEL single worksheet? You will have to split the information across multiple worksheets. Unfortunately there are no obvious ways to get around the 65535 row limit - you should either use a database application like MS Access or Excel's VBA, or some text file like CSV. But you can’t get that flexibility that Excel provides.

Let’s study the following example:

1. We have a price-list (or whatever) of some 200 000 lines.
2. We want to have it in one Excel file for our future work
3. We want to easily retrieve data searching across the whole range

The below is not a full description of IF-ISNA-VLOOKUP function. It only represents a particular case that is found to be very common among the business users and creative people.

So, let’s get down to work:

Our input 200 000-line file might be a .csv (or .dbf) and can look like this:



What we need to do is split the data by 65536 rows and put it onto several Excel sheets in one file.

In our case it should be 4 sheets (4 x 65536 will store us 262144 maximum rows).



There are some splitting programs but if you don't have them proceed as follows:
1. Save the initial file as Excel (e.g. Book1.xls). It will be truncated to 65536 rows. Great, first sheet is ready!
2. Open the saved Excel file and go to the last line (press Ctrl+Down) - copy the last row value
3. Open the initial file again, press Ctrl+F, Ctrl+V (Paste) to find the last saved row and then cut the above data
4. Save it as Excel (e.g. Book2.xls) -- next 65536 lines will be saved

5. Continue saving sheets until you reach the end of the file

6. Put all your sheets together in one Excel file

Your worksheet will look like:



Next we create a fifth worksheet named "Calculation" with a complex “IF(ISNA(VLOOKUP” formula on it:

=IF(ISNA(VLOOKUP(B3,'1'!A:J,2,FALSE)),"",VLOOKUP(B3,'1'!A:J,2,FALSE))&IF(ISNA(VLOOKUP(B3,'2'!A:J,2,FALSE)),"",VLOOKUP(B3,'2'!A:J,2,FALSE))&IF(ISNA(VLOOKUP(B3,'3'!A:J,2,FALSE)),"",VLOOKUP(B3,'3'!A:J,2,FALSE))&IF(ISNA(VLOOKUP(B3,'4'!A:J,2,FALSE)),"",VLOOKUP(B3,'4'!A:J,2,FALSE))

Yes, it looks complicated, but taking a closer look you will see how simple it is. It just repeats a part of itself to get through all the worksheets.

In the above formula we lookup a value from the cell B3, across all 4 sheets, searching in the range defined as A:J (from column A to column J), and retrieving the data from column number 2. We found a "Description"!

Changing the column number we can display the necessary data in any cell of our calculation sheet.

Tip
You can copy the "IF(ISNA(VLOOKUP" formula to Word application and use "Find and replace" feature to adjust the retrieved column number instead of typing it yourself 8 times.

Let me Download it as Excel!