Skip to main content

Excel - HLOOKUP and VLOOKUP Formulas


If you want to assign letter grades, such as A, B, C, D, E, or F, to students based on their average marks, you can use either the HLOOKUP or VLOOKUP formula in Microsoft Excel. These lookup formulas automatically compare a student's score with a grading table and return the corresponding letter grade. This method is much faster and more accurate than entering grades manually, especially when working with large data.

Suppose the students' average marks are listed in column E, and you want to show the corresponding letter grades in column F. The formula looks up each student's average mark in a grading table and returns the appropriate grade based on the score range.

  • The first argument is E2, which represents the cell containing the student's average mark. This is the value that Excel searches for in the grading table. When you copy the formula down to the remaining rows, Excel automatically changes the cell reference to E3, E4, E5, and so on, allowing each student's score to be evaluated individually.
  • The second argument is $I$2:$L$3, which represents the lookup table, also known as the table array. This table contains the score ranges in the first row and the corresponding letter grades in the second row. The dollar signs ($) make this an absolute cell reference, meaning the table's location remains fixed even when the formula is copied to other cells. Absolute references are essential whenever the lookup table stays in the same location because they prevent Excel from changing the table reference as the formula is filled down.

  • The third argument is 2, which tells Excel to return the values from the second row of the lookup table. Since the second row contains the letter grades, Excel returns the appropriate grade that matches the students' average mark. When creating the lookup table, always arrange the score ranges from the lowest value to the highest value. For example, the first row might contain 0, 50, 60, 70, 80, and 90, while the second row contains the corresponding grades F, E, D, C, B, and A. Organizing the values in ascending order ensures that the lookup formula returns accurate results when using an approximate match.

The main difference between HLOOKUP and VLOOKUP lies in the orientation of the lookup table. HLOOKUP (Horizontal Lookup) searches for values in the first row of a horizontal table and returns a value from a specified row below it. In contrast, VLOOKUP (Vertical Lookup) searches for values in the first column of a vertical table and returns a value from a specified column to the right.

Both formulas are useful for grading students, finding product prices, retrieving employee or patient information, and many other data lookup tasks. By using HLOOKUP or VLOOKUP, you can automate the grading process, reduce errors, and save significant time when working with spreadsheets containing large amounts of data in your workplace.

* Later, I will explain the new formula, XLOOKUP, which is the most powerful lookup formula.

* The top image is a screenshot of an Excel file.
Labels:

Comments

Post a Comment

Popular posts from this blog

My Journey: From Korea To Canada

About 27 years ago, my family and I made one of the biggest decisions of our lives—we immigrated to Canada. Like many newcomers, we came with hope, determination, and dreams for a better future. Our primary goal was to provide a better education for our children, a happier and more stable life for our family, and greater career opportunities for me. Although we were excited about the future, starting over in a new country was never easy. Everything was unfamiliar. We had to adapt to a different culture, a different way of life, and, most importantly, a new language. The language barrier made even simple daily tasks challenging. Every day became an opportunity to learn something new, and every small success felt like a big achievement. Before coming to Canada I had already built a solid career in the information technology industry in Korea. I worked for several IT-related companies, including an Apple reseller, a server company, a distributor of computer peripherals, software, and stor...
Post a Comment
Read more

My YouTube Channel about Digital Technology

I started my career as a ranger instructor before moving to Canada. Since then, I have taught a lot of students who were soldiers, employees, children, or college students from around the world. My students have come from North America, Africa, Asia, and Europe. Even without travelling by myself, I have been able to experience different cultures through my students.  I love teaching, communicating, and troubleshooting.  That's why I started the YouTube channel to help students learn, grow, and succeed. These are the current playlists of my YouTube channel. I'll try to upload as often as possible. If you need any new posts that you are interested in, let me know.  As you can see on the thumbnail, there are six different categories:  Photoshop HTML and CSS Excel Word Illustrator InDesign I'm also planning to add more categories soon, including AI and a career coach.  If you are interested in a specific topic, you can go directly to that playlist to save time. 1. ...
Post a Comment
Read more

Fiction - I Lost My Combat Helmet

In South Korea, virtually all healthy men are required by law to complete mandatory military service, typically between the ages of 18 and 28. Today the term is around 18-21 months, but in the 1990s it was considerably longer—roughly 27 to 30 months, before being gradually shortened in later years. This series is based on my own experience serving during that era. About to receive my permanent base assignment, I still couldn't get the word “soldier” to roll naturally off my tongue. The training uniform didn’t fit my body well, and my combat boots always felt a size too big or too small. To a private second class who had enlisted only a few weeks earlier, the military was still less a “place to live” and more a “place to endure.” Yet the reason I ended up participating in Team Spirit (1988) training was simple. It was because I could speak English better than other Korean soldiers. That was the only reason. Since it was a joint training exercise with the US military, translation ass...
Post a Comment
Read more