Introduction

In Vlookup or Hlookup formula in second input table range is used. The second input in Vlookup or Hlookup formula also called table_array. In the below image you can see for giving grades approximate Vlookup formula is used. In this Vlookup formula in second input, range I2:J8 has been used.

But you can replace table range with array values which will give same output. And above formula can be re-written as

=VLOOKUP(B2,{“Marks”,”Grade”;0,”Fail”;35,”C”;50,”B”;70,”A”;85,”A+”;100,”A+”},2,TRUE)

Here for range I2:J8 array list {“Marks”,”Grade”;0,”Fail”;35,”C”;50,”B”;70,”A”;85,”A+”;100,”A+”} is used.

Why to use array values?

One may question why to use array values, here answer is to make Vlookup formula independent of table range. In another word Reference table got embeded in Vlookup formula and you can copy paste this formula directly in new workbook without worrying about reference table range.

One may say typing array values is time consuming  but don’t worry I am going  to show you better and quick way to convert range into array values.

Yes, this approach is more suitable if reference table  is small like above image example.

Understanding array values

From the above image you can understand number of columns and rows  an array contains. By understanding array values you can do some editions if required.

Seven blocks separated by semi colon means seven rows and in each block two values are separated  by comma means two columns.

Quickly converting a range into array values

Write formula to select a range in any blank cell then press function key F9 instead of ENTER to make it array values then press ENTER.

Now you can copy array values excluding equal to sign and use it in a formula.