Excel Tricks with Tejraj

In this blog post we will see how we can apply HLOOKUP in excel. For this we have prepared a dummy data as shown in the below image. This data is divided into two groups. Blue Highlighted Data : This data contains Student Names and marks obtained by each student in the subject Maths, English and Science. In the last row we have calculated total marks obtained by each student. Red Highlighted Data : This data contains similar headers as that of Blue Highlighted data. We will apply HLOOKUP in these cells. Methods to find HLOOKUP Function on Excel Screen: We can find HLOOKUP function using two different methods. In this first method, HLOOKUP function can be found in “Lookup & Reference” which we can find in “Function Library” group under “Formulas” tab (Below screenshot for your reference). Once we click on this “HLOOKUP”, one dialog box named as “Function Arguments” will appear on excel screen. There are total 04 arguments available for this HLOOKUP function. Once we enter all the 04...

In this blog post we will learn to about OFFSET function in excel. OFFSET function will give us a reference of a cell , or a cell range based on provided number of rows and number of columns. Basically, OFFSET function will shift the original reference to up, down, left, or right based on provided number of rows and columns and gives us new reference. Where to find OFFSET function on Excel Screen: OFFSET function can be found under “Lookup & Reference” function category under “Formulas” tab and under “Function Library” group as shown in below image: Once we click on “Lookup & Reference” function category, we can see list of various Lookup & Reference functions available in excel. OFFSET function is highlighted in blue in below image. Once we click on OFFSET option as highlighted above, we will get the function argument dialog box as shown in below image: Syntax of OFFSET Function: The Syntax of OFFSET function is as below: =OFFSET(reference, rows, cols, [height], [width]) ...

In this blog post we will learn to about INDEX function in excel. We can use INDEX function to find value which is present at the intersection of provided row number and column number in g given cell range . For example, we want to find our which value is present at the intersection point of row 3 and column 4 in a given table then applying INDEX function is a perfect solution. Where to find INDEX function on Excel Screen: INDEX function can be found under “Lookup & Reference” function category under “Formulas” tab and under “Function Library” group as shown in below image: Once we click on “Lookup & Reference” function category, we can see list of various Lookup & Reference functions available in excel. INDEX function is highlighted in blue in below image. Once we click on INDEX option as highlighted above, we will get the select argument dialog box as shown in below image. As this function has multiple argument lists, we must select one of the two argument lists appearing...

In this blog post we will learn to about MATCH function in excel. We can use MATCH function to find relative position of a provided item in a cell range . For example, we have listed name of few countries in one column, and we want to find out the relative position of specific country in that cell range then we can do so by using MATCH function. Where to find MATCH function on Excel Screen: MATCH function can be found under “Lookup & Reference” function category under “Formulas” tab and under “Function Library” group as shown in below image: Once we click on “Lookup & Reference” function category, we can see list of various Lookup & Reference functions available in excel. MATCH function is highlighted in blue in below image. Once we click on MATCH option as highlighted above, we will get the function argument dialog box as shown in below image: Syntax of MATCH Function: The Syntax of MATCH function is as below: =MATCH(lookup_value, lookup_array, [match_type]) Arguments of ...