Use conditional formatting rules in Google Sheets

-
Image
  Cells, rows, or columns can be formatted to change text or background color if they meet certain conditions. For example, if they contain a certain word or a number. On your computer, open a spreadsheet in  Google Sheets . Select the cells you want to apply format rules to. Click  Format     Conditional formatting . A toolbar will open to the right. Create a rule. Single color : Under "Format cells if," choose the condition that you want to trigger the rule. Under "Formatting style, choose what the cell will look like when conditions are met. Color scale : Under "Preview," select the color scale. Then, choose a minimum and maximum value, and an optional midpoint value. To choose the value category, click the Down arrow  . Click  Done . Example A teacher can highlight test scores to see which students scored less than 80%. On your computer, open a spreadsheet in  Google Sheets . Select the test scores. Click  Format     Conditional...
Post a Comment

HLOOKUP Function in Excel With Example

-

 

HLOOKUP in Excel – With Examples

HLOOKUP function in Excel is a sibling of the VLOOKUP function. The H in the HLOOKUP stands for “Horizontal” and hence it is often called Horizontal Lookup.

HLOOKUP is a very useful function for creating horizontal lookups, but as most of the tables that we deal with are vertical hence this function is not very popular.

The task of the HLOOKUP function is to search for a value in the topmost row of a table, and then return a corresponding value in the same column from a row you specify.

Definition and Syntax of HLOOKUP Function in Excel

Microsoft Excel defines HLOOKUP as a function that “looks for a value in the top row of a table or array of values and returns the value in the same column from a row you specify”.

Syntax of Excel HLOOKUP

The syntax of the HLOOKUP function in Excel is as follows:

=HLOOKUP(lookup_value,table_array,row_index_number,range_lookup)

How to Use HLOOKUP in Excel

Now, let’s understand how to use this function in Excel.

Consider, we have a Student Table as shown below:


Objective: In this case, our objective is to fetch Steve’s marks in English using Horizontal Lookup.

1- So, we will try to apply an HLOOKUP to get the result.



2- ‘lookup_value’: As we know that we have to find the marks of Steve, so our ‘lookup_value’ will be a “Steve”.



3- ‘table_array’: In this argument, we give the reference of our table i.e. A1:I4.



4- ‘row_index_num’: The ‘row_index_num’ in this case, would be 4 as here we have to fetch a value from the fourth row of the table.



5- ‘range_lookup’: ‘range_lookup’ will be FALSE as here we only want to fetch the exact match value.



The result of this formula is 61.


Example-

Now, let’s see some examples of Horizontal Lookup Function.

Using the below table, find the Marks in English of a student who has got 75 marks in Science.



We can use this formula to get the result:
  =HLOOKUP(75,B2:I4,3,FALSE)


The result of this formula is 40.

Explanation:

  • The first argument to the function i.e. ‘lookup_value’ = 75 (Marks of the student in Science)
  • Second argument i.e. ‘table_array’ = B2:I4 (Range of student table)
  • Third argument i.e. ‘row_index_num’ = 3 (the row number whose value the HLOOKUP function should return)
  • Fourth argument i.e. ‘range_lookup’ = FALSE (Signifies that we only want the exact match)

Comments

Post a Comment

Popular posts from this blog

Use conditional formatting rules in Google Sheets

-
Image
  Cells, rows, or columns can be formatted to change text or background color if they meet certain conditions. For example, if they contain a certain word or a number. On your computer, open a spreadsheet in  Google Sheets . Select the cells you want to apply format rules to. Click  Format     Conditional formatting . A toolbar will open to the right. Create a rule. Single color : Under "Format cells if," choose the condition that you want to trigger the rule. Under "Formatting style, choose what the cell will look like when conditions are met. Color scale : Under "Preview," select the color scale. Then, choose a minimum and maximum value, and an optional midpoint value. To choose the value category, click the Down arrow  . Click  Done . Example A teacher can highlight test scores to see which students scored less than 80%. On your computer, open a spreadsheet in  Google Sheets . Select the test scores. Click  Format     Conditional...
Read more