Home

HLOOKUP

HLOOKUP Function - Examples, HLOOKUP Formula in Exce

How to use the Excel HLOOKUP function Excelje

HLOOKUP function - prod

Excel HLOOKUP with partial match (wildcard characters) As is the case with VLOOKUP, Excel's HLOOKUP function allows using the following wildcard characters in the lookup_value argument:. Question mark (?) to match any single character; Asterisk (*) to match any sequence of characters; Wildcards come in handy when you want to pull information from a database based on some text that is part of. HLOOKUP(search_key, range, index, [is_sorted]) search_key - is the value that the HLOOKUP function uses to search.; range - is the reference to the range of cells that we are performing a search on. Google Sheets application looks for the key in the first row of the range. index - this is the row index of the cell within the range, whose value the function returns Hlookup in Excel. Hlookup in excel is very similar to Vlookup in excel, Hlookup is a referencing worksheet function in excel which uses a reference and finds and matches the value from a row instead of a column, that is why the name was given to this formula as Hlookup which means horizontal lookup where we search for data horizontally in rows Use HLOOKUP to sum values based on a specific value. Here I introduce some formulas to help you quickly sum a range of values based on a value. Select a blank cell you want to place the summing result, enter this formula =SUMPRODUCT(HLOOKUP(B15,A1:M12,{2,3,4,5,6,7,8,9,10,11,12},0)) and press Enter key, now you get the summing result.. Tip: In the formula, B15 is the value you want to sum based. Use HLookup when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLookup when your comparison values are located in a column to the left of the data that you want to find. Syntax. expression.HLookup (Arg1, Arg2, Arg3, Arg4

Excel VLOOKUP vs HLOOKUP | Difference Between Vlookup vs

HLOOKUP in Excel (Formula, Examples) Uses of HLOOKUP

MS Excel: How to use the HLOOKUP Function (WS

Hlookup Function Examples Hlookup Example 1 - Exact Match Lookup. Cells A2-F6 of the spreadsheet below, show the exam scores for 5 students in 4 different subjects. If you want to look up a specific score (e.g. Biology) for one of the students (e.g. Ed), this can be done using the Hlookup function, as shown in cell B10 of the spreadsheet The HLOOKUP and VLOOKUP functions are grouped in the lookup category of functions. The function looks for a certain value in the first row (HLOOKUP - (h)orizontal) or column (VLOOKUP - (v)ertical) of a table and returns any value from the same column (HLOOKUP) or row (VLOOKUP) HLOOKUP (lookupvalue; datatable; rowindex; mode) lookupvalue is a value (number, text or logical value) to look up in the top row of the range/array datatable. When a value is matched in the top row, HLOOKUP returns the corresponding value (in the same column) in the rowindexth row of datatable, where rowindex = 1 is the top row HLOOKUP in Excel stands for 'Horizontal Lookup'. It is a function that makes Excel search for a certain value in a row (the so called 'table array'), in order to return a value from a different row in the same column

HLOOKUP in Excel - With Example

The HLOOKUP function searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. You can use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows The Excel HLOOKUP formula is just like the VLOOKUP formula, only the table you are looking up is laid out in a horizontal list, rather than vertically. Hence the 'H' for horizontal, and 'V' for vertical. Just like the VLOOKUP, the HLOOKUP function can be used to find an exact match or used in a sorted list

In cell E2, enter a formula using the HLOOKUP function as follows to determine a student's potential base hourly rate (which is based on the number of years of post-secondary education): a. Use a structured reference to look up the value in the Post-Secondary Years column. Retrieve the value in the 2nd row of the table in the range P13:U14. Can I execute a formula inside a formula. My colleague is want to to do this: If he selects fruit, he wants his hlookup to return for the selected hlookup for fruit, but if he selects meat, he wants his hlookup to return the selected hlookup for meat. Example.. The HLOOKUP Function: The formula in cell E19 (circled in red) is: = HLOOKUP(E16,A3:N14,2,FALSE). The Microsoft definition of the HLOOKUP function: 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 The HLOOKUP function in Excel makes it easier to find data located in a predefined table. The H in HLOOKUP stands for horizontal and refers to the first row of a table. The HLOOKUP function is particularly useful when working with large tables. It eliminates the need to spend a lot of time looking for specific values and provides a.

The Excel HLOOKUP function searches for a specific value in the first row of the selected range (table) and returns a value that resides in the same column as the lookup value from a specific row. Learn how to apply the Excel HLOOKUP function using Excel and VBA What it means: =HLOOKUP ( this value, in this list, and get me value in this row, [Exact Match/FALSE/0]) Ever had a horizontal table and you want to search for values in the table easily? I'm sure you do! There is a simple way to do this with Excel's HLOOKUP function! This is very similar to the VLOOKUP Function

How to use the HLOOKUP function in Excel + Examples [2021

The HLOOKUP function in Excel is the less popular cousin of the VLOOKUP function. The H in HLOOKUP stands for horizontal. Because Excel data is typically vertically oriented, most situations require a vertical lookup, or VLOOKUP. However, some data structures are horizontally oriented, requiring a horizontal lookup; thus, the HLOOKUP function comes in handy. The [ Combine VLOOKUP and HLOOKUP. Sometimes you need to make the column reference of your VLOOKUP formula dynamic. The best way is to use the VLOOKUP function with the MATCH function combined please see this article: VLOOKUP & MATCH Combined - Excel & Google Sheets, but now we will show another usable method with a VLOOKUP-HLOOKUP combination =VLOOKUP(G3,A5:E10,HLOOKUP(G4,B3:E4,2,FALSE),FALSE) We will press the enter key; Figure 3- Result of the VLOOKUP and HLOOKUP Functions. Explanation. In this formula, the horizontal value (2016) is looked up with the HLOOKUP section of the formula. The returned value by HLOOKUP is 5 because HLOOKUP searches for 2016 in the range B3:E4 in ROW 2 1/12/2014 2/12/2014 3/12/2014 4 5 6 =hlookup(Date?,a1:c3,2,True) Say I have dates in Row 1 in descending order with values in row 2. Can I use hlookup to find the value for a certain date. I am getting an error, but is seems straightforward. What am I missing

Excel HLOOKUP: A Step-by-Step Tutorial on How to Look Up

The Horizontal lookup function, HLOOKUP in excel is used to find the specific value in a row for the table that is arranged horizontally.. On the other hand, if table data is arranged vertically then we use VLOOKUP (vertical lookup) function.. In the HLOOKUP function, lookup value is provided that is searched in a table or array of values and it returns a value in the same column from a. This is too bad, because . 1. INDEX-MATCH is much more flexible than Excel's lookup functions. 2. At its worst, INDEX-MATCH is slightly faster than VLOOKUP; at its best, INDEX-MATCH is many-times faster. I can think of only two reasons you ever should use VLOOKUP (or HLOOKUP, which does the same thing, but sideways)

How to use VLOOKUP, HLOOKUP and INDEX MATCH in Excel

Formula: =hlookup (1, {SORTN (search (F3,B3:E));B2:E2},2,0) You can use this same Hlookup formula to find the value in the header row of any cell in Google Sheets. For that, I mean for your source data, you just need to change the table reference in the formula that is within the Curly Brackets. You must learn to code this formula then only you. The HLOOKUP Function Hlookup stands for horizontal lookup. It searches for a value in the top row of a table. Then returns a value a specified number of rows down from the found value. It is the same as a vlookup, except it looks up values horizontally instead of vertically. (Notice how the formula inputs appear) HLOOKUP Function Syntax and Input The HLOOKUP Function: The formula in cell E19 (circled in red) is: = HLOOKUP(E16,A3:N14,2,FALSE). The Microsoft definition of the HLOOKUP function: 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

VLOOKUP () and HLOOKUP () return the value in a cell directly; MATCH () uses a similar approach to finding the matching cell as the lookup functions but doesn't return the value, instead, it returns the position of the cell in the list of cells. As we will see, this is why MATCH () is often used with INDEX () the proper way to call an Excel function from VBA call is for example: cells (1,2) = Application.WorksheetFunction.VLookup (123,Range (A1:C100),3,FALSE) anyway if you'd rather use this string approach the problem in the first try is that. =HLookup ( & lookupValue _ & ; & tableArray & ;1;FALSE) results in the string

HLOOKUP (Fungsi HLOOKUP) - Dukungan Offic

  1. Hlookup function is the cousin of the Vlookup function and is used to locate information horizontally. In fact the H in Hlookup stands for horizontal. The two differences between the Hlookup and Vlookup functions is Hlookup uses rows instead of columns to locate information. Second difference is the answer is located beneath the.
  2. The HLOOKUP function is the horizontal lookup, used for searching along rows. Not as well known as its sibling VLOOKUP, but useful for examples like below where the headers are in column A, and the data is along rows 4 and 5. XLOOKUP can look in both directions - down columns and also along rows. No longer do we need two different functions
  3. Fundamental considerations with reverse HLOOKUP alternatives. Similar to the caveat in the reverse VLOOKUP article, the first option (LOOKUP and SEARCH) will deliver erroneous results, if the data array contains some blank cells.Thus in this instance the second approach, the INDEX and MATCH for a reverse HLOOKUP is the only option.. As the video proved, it is paramount to sustain uniform range.

Excel HLOOKUP function with formula example

  1. How to use Vlookup and HLookUP to set a range for CountIF. Ask Question Asked 4 years, 10 months ago. Active 2 years, 5 months ago. Viewed 3k times 0 I searched through the internet and StackOverflow but I did not find the exact relevant issue being resolved. So I am trying to resolve the following issue..
  2. vlookup & hlookup The famous VLOOKUP is widely used by many professionals building data models in Excel. Its purpose is to search for specific data, and return the corresponding result
  3. WorksheetFunction.HLookup (Excel) Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row that you specify in the table or array. Use HLookup when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows
  4. HLOOKUP does the exact same work as VLOOKUP but horizontally in an array. Let's understand the HLOOKUP function syntax and an example to illustrate the function. HLOOKUP Function in Excel. HLookup Stands for Horizontal Lookup. Same as Vlookup it looks for a value in a table but Row wise, not column wise. Syntax of HLOOKUP
  5. HLOOKUP is the exact same function, but looks up data that has been formatted by rows instead of columns. LOOKUP and related functions are commonly used for business analytics in Excel as a way of slicing and dicing data for analysis
  6. I use the HLOOKUP formula to extract specific data into a seperate spreadsheet, but I have had to do a calculation in the cell next to the original data and now need to pull that amount into the seperate sheet. (I cannot put the new calculation above or below only beside)
  7. HLOOKUP. Details. In this lesson you will learn HLOOKUP function. HLOOKUP works similarly to the VLOOKUP function. HLOOKUP is a lookup and reference function. This function retrieves data from the table horizontally. Most of the tables in Excel, create a vertical, so this feature is rarely used. Despite it, I think it's worth it to know HLOOKUP

Hello Friends,In this video you will learn how to use HLOOKUP with MATCH function with example in Excel.Please download this excel file from below given link.. The VLOOKUP and HLOOKUP formula in Excel is the two most important and widely used functions of Excel. These two functions are the lookup functions in Excel that searches for the data in the table array and returns the required output I am trying to combine OFFSET and HLOOKUP but have some probs. I have a HLOOKUP that returns the correct value.....however instead of pulling that value... I would like to pull the value to the right 1 column HLOOKUP is an Excel function used to lookup up and return information based on the table_array. The HLOOKUP stands for horizontal lookup and searches data arranged in rows. The HLOOKUP searches below the lookup_value which is always located in the top row (position 1) of the lookup_table. The return value is retrieved based on the row_index_num.

Example 1: =HLOOKUP (4,B4:F6,2,FALSE) HLOOKUP Looks for the lookup value (4) in the top most row of the lookup range (first row, ie; B4:F4) and returns its corresponding value in the second row (as we mentioned row index as 2 in the HLOOKUP formula). So the result will be 16 (value found at 4th column, 2nd row HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) For your problem, the table_array is the product pricing table. The function always looks in the first row of the table to find the lookup_value A. Pengertian Rumus Excel VLOOKUP & HLOOKUP. Rumus Excel VLOOKUP dan HLOOKUP adalah rumus yang paling sering digunakan membuat lookup function untuk melihat data secara vertikal dan horizontal. Berbeda dengan rumus LOOKUP yang dapat mencocokkan data teks maupun numerik, sedangkan rumus VLOOKUP dan HLOOKUP khusus untuk mencocokkan data numerik. . Penggunaan rumus VLOOKUP dan HLOOKUP merupakan. How to use VLOOKUP & HLOOKUP functions at a basic and advance level. Learn the whole lookup functionality from Microsoft Excel expert. Learn how to combine VLOOKUP & HLOOKUP with IFERROR and other powerful features. Learn how to troubleshoot & solve VLOOKUP errors. How to add data validation to make your VLOOKUP & HLOOKUP Formulas more easier.

HLOOKUP searches for a value in the topmost row and returns a value from the same column. The description text in Excel summarizes it quite well: Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. That means you got a table with data and. Hlookup is a tool used in Excel. It stands for horizontal lookup, as the name indicates it lookup for the data horizontally. This tool is used to search the data that are arranged in rows but most of the data are arranged in columns so it is the least used tool The above Hlookup formula searches across the first row for the key Mar in this range and returns the value from the second row, i.e. 4. See the column Index in Hlookup, i.e. 2 (second row). This way you can use the Vlookup and Hlookup Combination in Google Sheets. Two-Way Lookup in Google Sheets - Using Vlookup and Matc HLOOKUP in Excel. The HLOOKUP function is the not as cool younger sibling of VLOOKUP. Learning how to do a HLOOKUP in Excel is the same as a VLOOKUP, the data is instead looked up horizontally rather than vertically. There is a LOOKUP() but this is only hanging around as it's compatible with old versions of Excel

Hello, I am trying to use excel to build an attendance consolidation sheet. Each day has 7 hours (1 through 7). The user has to fill in the absentee roll numbers (separated by comma) in the Attendance entry sheet. This data is pulled into the sort sheet and separated using a TRIM-MID-SUBSTITUTE. =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) lookup_value: This signifies the lookup value that you are trying to find out in the leftmost column of the spreadsheet or table. table_array: This is the whole data given in the table or entire table in which you are going to find out the value. row_index_num: It is a row index number in which you are going to fetch the.

Hàm HLOOKUP chỉ có thể tìm ở hàng trên cùng của table_array. Nếu bạn cần tìm ở những vị trí khác, hãy sử dụng công thức Index hoặc Match. Nếu range_lookup được đặt là TRUE hoặc bỏ trống (kết quả khớp tương đối), các giá trị ở hàng đầu của table_array phải được. =HLOOKUP(Lookup_Value, Search_Area, Row_Number, [Approximate_Match]) Lookup_Value (required) - The Lookup_Value is the value we know and will use to specify the correct column to search. For the HLOOKUP this will likely be a column header. The lookup_value can be a cell reference, number, a text string, or other type of value Explanation of the formula: Formula used is =HLOOKUP(B8,B1:F6,5,0). B8 is the lookup value.. B1: F6 is the cell range or table in which the formula will lookup for the value.. 5 is the row number.. 0 for an exact match of the lookup value.. And so we got the result 48. Example #5. We will look into some examples where we use an approximate match that is True or 1 HLOOKUP() function is used when the user tries to find a value in a table by matching a lookup value in the first row of the table. Generally, the user wants an exact match. This function searches for a value horizontally as its name means. Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]

The HLOOKUP function is one of the lookup and reference functions. It is used to perform the horizontal search for a value in the top row of a table or an array and return the value in the same column based on a specified row index number. The HLOOKUP function syntax is: HLOOKUP (lookup-value, table-array, row-index-num[, [range-lookup-flag. We want to determine the corresponding grade for the score of 85. Let us follow these steps: Step 1. Select cell C6. Step 2. Enter the formula: =HLOOKUP (B6,B2:G3,2,FALSE) Step 3: Press ENTER. Our lookup_value is cell B6, which contains the specific score we want to search: 85. Our table_array is the range B2:G3 HLOOKUP formula. Hlookup refers to Horizontal lookup. Its purpose is to look up a value or text horizontally across a row. When the value is found, it will return a value in another row that corresponds to the column of that value. Assuming that you have a table which presents the ages in the top row (header row) and the left column presents. The HLOOKUP function lets you search the top row in a data range for a value and return another value on the same column in a row you specify. HLOOKUP stands for horizontal lookup and your data (table_array) must be organized into records, a record on each row. To search records excel must look horizontally in the top row of the data array

2015-01-01. Categories: Functions, Lookup & Reference. The english function name HLOOKUP () has been translated into 19 languages. For all other languages, the english function name is used. There are some differences between the translations in different versions of Excel excelで横方向に検索したいときはhlookup関数を使用します。初めてhlookup関数を触る人に向けて引数を1つずつ分解して解説しています。またvlookup関数との違いも説明しています。違いを理解して使い分けましょう Hàm Hlookup về cơ bản cũng có cú pháp hàm và tính năng như hàm Vlookup, đó là giúp người dùng tìm kiếm dữ liệu có trong bảng Excel, với những điều kiện hoặc thông tin cho trước. Dưới đây là hướng dẫn sử dụng hàm Hlookup chi tiết HLOOKUP and VLOOKUP are functions in Microsoft Excel that allow you to use a section of your spreadsheet as a lookup table.. When the VLOOKUP function is called, Excel searches for a lookup value in the leftmost column of a section of your spreadsheet called the table array. The function returns another value in the same row, defined by the column index number

How to use the HLOOKUP function in Google Sheets - Sheetgo

  1. Hlookup. In a similar way, you can use the HLOOKUP (Horizontal lookup) function. Match. The MATCH function returns the position of a value in a given range. Explanation: Yellow found at position 3 in the range E4:E7. The third argument is optional
  2. The syntax of the VLOOKUP and HLOOKUP functions. The functions have 4 arguments: Lookup value: WHAT we are looking for - the desired parameter (numbers and / or text) or a reference to the cell with the desired value; Table array: WHERE we are looking for - an array of data where the search will be performed (for VLOOKUP - the searching of the value is carried out in the FIRST column of.
  3. Overview of VLOOKUP & HLOOKUP Many of us are familiar with the VLOOKUP function. The VLOOKUP and HLOOKUP formula in Excel allow you look up a value in one column or row and return a corresponding value from a different column or row. VLOOKUP is a vertical lookup and HLOOKUP is a horizontal lookup. We [
  4. HLOOKUP Multiple Rows. Using a HLOOKUP Formula i'm wanting to know if it is possible to record data from more than one row. For example: =HLOOKUP (M8,M11:N14,2,FALSE) Returns the data from Row 2 of the table
  5. VLOOKUP and HLOOKUP. Here is an example to illustrate vertical lookup (VLOOKUP) and horizontal lookup (HLOOKUP) functions. These functions are used to translate a number or other value into something which is understandable. For example, you can use VLOOKUP to take a part number and return the item description
  6. hlookup函数是excel等电子表格中的横向查找函数,它与lookup函数和vlookup函数属于一类函数。用hlookup函数可以在表格或数值数组的首行查找指定的数值,并返回表格或数组中指定行的同一列的数值,hlookup中的h代表行

HLOOKUP Function Example. Let's look at the following example using the HLOOKUP function. We have sales data broken up in quarters for 2019. We want Excel to look for the result in row 4 under Q3 2019. The syntax is explained below. =HLOOKUP(Q3 2019,A1:E5,4,TRUE) value - Q3 2019 - Parenthesis are required for text Can replace both VLOOKUP and HLOOKUP functions; Easier to audit with F2 than VLOOKUP or HLOOKUP . Examples of LOOKUP vs VLOOKUP. Below are two screenshots of the LOOKUP vs VLOOKUP functions being used in Excel. As you will see in both examples, there is a simple table with some people's names, countries, and ages VLOOKUP Function Syntax & Arguments. There are four possible parts of this function: =VLOOKUP ( search_value, lookup_table, column_number, [ approximate_match] ) search_value is the value you're searching for. It must be in the first column of lookup_table. lookup_table is the range you're searching within. This includes search_value HLOOKUP with #N/A result I have a spreadsheet that has 3 tabs that employees use to track counts of different items. I want to create a summary of the total counts, however, not all of the tabs have the same column headers so when I use an HLOOKUP formula some of my rows return a #N/A result

HLOOKUP is a Horizontal lookup to search the value in the topmost rows. It is an Excel function, which helps the users to search and retrieve the data from the topmost row in an Excel worksheet. This function runs a bit differently than the other Excel functions The HLOOKUP function has three required arguments. Fill in the function arguments. Lookup_value: The value to be found in the first column. It can be a value, a reference, or a text string. Table_array: The entire cell range or table from which data will be retrieved

Contoh Soal Tentang IF, Vlookup dan Hlookup Plus

XLOOKUP is a new function is Office 365 and is a new and improved version of the VLOOKUP/HLOOKUP function. It does everything VLOOKUP used to do, and much more. XLOOKUP is a function that allows you to quickly look for a value in a dataset (vertical or horizontal) and return the corresponding value in some other row/column HLOOKUP ย่อมาจาก Horizontal Lookup ต้องใช้กับตารางข้อมูลแนวนอน. ตารางข้อมูลแนวตั้ง VS ตารางข้อมูลแนวนอ In Microsoft Excel, Hlookup or Horizontal Lookup Function is generally used to extract data from a table or an array based on searching for a specified value in the topmost row and the corresponding column. In this article, you'll get to learn how you can use this Hlookup Function efficiently in Excel

HLOOKUP in Excel How to Use HLOOKUP Function

Apr 14, 2014. #1. I have many HLOOKUP formulas in a large Excel model. In the formula HLOOKUP (A4,B10:D30,20), the row index number is 20. If I then insert a row at A12, I want the formula to reference row index number 21--but the formula doesn't automatically adjust. This is very important, because I have numerous lookup ranges in which I want. hello folks, Is it possible to formulize reverse HLOOKUP as following: In B5, I can use HLOOKUP to get the value in Row 2. But can I do the other way around, define value in B8 and B9, and get the label in Row 1? I tried: =HLOOKUP (B8,A1:D2,2) and it returned me #N/A. Tuesday, January 7, 2014 2:03 AM. Daniell.Chong

How to use HLOOKUP to sum a range of values in Excel

Using the HLOOKUP function. The syntax of the HLOOKUP function is as follows: = HLOOKUP ( lookup_value, table_array, row_index_number, range_lookup (optional)) Much like VLOOKUP, HLOOKUP takes a lookup_value argument, which defines what the function will be searching for. It also takes a table_array argument, which specifies a table of data on. 1. For example, the VLOOKUP function below looks up the first name and returns the last name. 2. If you change the column index number (third argument) to 3, the VLOOKUP function looks up the first name and returns the salary. Note: in this example, the VLOOKUP function cannot lookup the first name and return the ID

Difference Between HLookup and VLookup In Excel, you often need to look up data in a table which can be hectic at times, especially when it comes to large database tables. Excel is the most widely-used spreadsheet software which not only handles basic data computations but is able to analyze large set of data. Excel uses numerous functions to analyze [ The Course is very rich in information that will make you Professional in using VLOOKUP & HLOOKUP functions. The course is structured into 03 Sections. In Section 1 : We will Introduce VLOOKUP & HLOOKUP functions course; We also going to see the VLOOKUP definition; We will learn the Fundamentals of VLOOKUP & HLOOKUP ; In Section 2

Highline Excel 2013 Class Video 22: CHOOSE function to

HLOOKUP and VLOOKUP functions are very useful functions on their own but if you combine them together you can do even more. If you are using VLOOKUP rather than having the two columns one with the value to lookup and the other with the value to retrieve with the HLOOKUP you could get it to get values from more than one column HLookup Range: Select the range that captures the other value by changing range reference (B4:D4) in the VBA code to any range in the worksheet, that doesn't conflict with the formula. Explanation about how to apply a two dimensional lookup using a combination of an Excel VLOOKUP and HLOOKUP functions Hi Rob, >> I would like to do an Hlookup in the Columns field that looks for the number in the ID field from the first spreadsheet If I understand correctly, you may add a row at the bottom of the second sheet to count the number of x. Please adjust the data range(B4:b17) to fit your situation