I am currently working on a project and have to transfer the data from one worksheet to another. I am not able to use copy and paste. Neither can I use MSQuery because there are hundreds of columns in the data set and it is an one off exercise. As such, I decided to use to HLOOKUP to lookup the relevant column header and picked up the relevant data below that column. There are almost 1000 rows and it is not possible to change the index rows for one thousand rows. So I combined the HLOOKUP formula with the ROW formula to do the quick transfer. The formula is as follows:

=HLOOKUP(B$1,Sheet1!$A$1:$BBI$1000,ROW(),FALSE)

The above formula will make blanks become zero so I modify the formula to make blanks returned back as blank. I use the IF formula to do this:

=IF(HLOOKUP(B$1,Sheet1!$A$1:$BBI$1000,ROW(),FALSE)=””,””HLOOKUP(B$1,Sheet1!$A$1:$BBI$1000,ROW(),FALSE))

Once transferred, I removed the formula using the normal Copy and Paste Value function. Now that’s when blanks are treated differently. If I select GOTO SPECIAL Blanks, I will not find any. But if I am to use the FIND function to find blanks or nothing, I will find all the blanks.

So it is important to learn how to do both and try them out in different situation. The alternative was a time saver for me because it found almost one million blank cells for me.