Automatically expand the VLOOKUP data range

VLOOKUP Automatically Expend Data Range

The 2nd argument in the VLOOKUP function is the table_array.  This is the range of cells which contains the data to be used within the VLOOKUP function.    For example, in the code below A1:C9 is the table_array. =VLOOKUP(“O Redding”,A1:C9,3,FALSE) When using the VLOOKUP function, we often know how many rows there are in the data, … Read more

How to VLOOKUP row and column

VLOOKUP with rows and columns

There are times when our data is laid out in columns and rows. In these circumstances, we may need to VLOOKUP row and column at the same time. Look at the example below, how can we look up the Low for Jun? This post will show you how. VLOOKUP row and column with VLOOKUP & MATCH … Read more

Using wildcards with VLOOKUP

VLOOKUP with wildcards

Have you ever played Scrabble?  It is the game where you receive points for constructing words from letters printed on small plastic tiles.  In my opinion, the best tile to get is always the blank tile.  It doesn’t score any points, but it can be used to represent any letter at all.  Therefore, if you … Read more

VLOOKUP with multiple criteria

VLOOKUP with multiple criteria

Most Excel users think that VLOOKUP can only be used with a single criterion; until a few months ago I believed this was also the case.  In the past I had used INDEX/MATCH or helper columns because I had not appreciated how much could be achieved with the VLOOKUP & CHOOSE combination.  We have already … Read more

VLOOKUP: Change the column number automatically

VLOOKUP change column number automatically a

Unfortunately, VLOOKUP, whilst powerful, simple and easy to use, is one of the least flexible functions in Excel.  Just imagine for a moment that we have entered the following formula into Cell E6 (see screenshot below). =VLOOKUP(E4,A2:B10,2,0) This formula is returning the value from the 2nd column (Column B) – result 21. But, what happens … Read more

How to VLOOKUP to the left

vlookup how to lookup left

VLOOKUP is a very useful formula; most advanced Excel users still encounter situations where it would be a suitable solution.  But a basic VLOOKUP has one major flaw; it cannot lookup a value to the left.  The value being looked up must always be in the first column and the value returned must always be … Read more

VLOOKUP: How to calculate faster

VLOOKUP How to calculate faster

I hardly ever use an approximate match with VLOOKUP due to the risk of getting an incorrect result.  But I learned this little trick – which provided almost unbelievable speed increases. The problem With a VLOOKUP an exact match (i.e. including FALSE as the last argument) is a slow method of calculation.  It will search … Read more