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.
