Home // Blog & Excel Tips // Vlookup counts hidden columns

Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!

2. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'

 

 

Vlookup counts hidden columns

Comments Closed
Uncategorised

If you are familiar with the VLOOKUP command in Excel you may be interested to know that Vlookup counts hidden columns as well as the visible columns when deciding which columns information to bring back. So perhaps your VLOOKUP is bringing back the wrong column? This could be the reason.

NEW: Go to  VLOOKUP Explanations with downloadable exercises and detailed solution

In the VLOOKUP syntax of

VLOOKUP(lookup value, table array,col index number, [range_lookup])

the Col Index Number portion specifies which column of the highlighted table array should be pulled through. It is important to note that when Excel counts the number of columns in the table array it INCLUDES any hidden columns.

So if you cannot understand why the column number used it different from what you counted, it may be that there are some hidden columns.

Related

Financial model review on vlookup, hlookup, lookup