Excel Formula to Lookup Value in Table (3 Suitable Examples)

When working with an enormous dataset, it is a very frequent need for us to find some value in the table of the dataset. Finding it manually and extracting the corresponding values of this data is very time-consuming and tiring. Rather, we can use an Excel formula to look up a definite value in a table and other corresponding values of that value too. In this article, I will show you effective Excel formula to lookup a value in a table.


Download Practice Workbook

You can download our practice workbook from here for free!


3 Examples of Excel Formula to Lookup Value in Table

Say, we have a table data of 10 students’ obtained marks in an exam. We also have the declared grading system of the institution. Now, we have to find grades for each student in the existing grading system according to the obtained marks of the students. We can use any of the formulas below to lookup the grade value from the grading system according to the student’s obtained marks.

3 Examples of Excel Formula to Lookup Value in Table: Sample Dataset

In this article, I have used the Office 365 version of Microsoft Office. But, you can use any other version of Excel according to your needs. If you face any problems regarding versions, please feel free to comment below.


Formula 1: Using Excel LOOKUP Function

The simplest way to obtain your desired result is to use the LOOKUP function. Follow the steps below to use this formula to get your result.

📌 Steps:

  • First and foremost, click on the D5 cell.
  • Afterward, insert the following formula.
=LOOKUP(C5,$F$5:$G$10)
  • Subsequently, hit the Enter button.

  • As a result, you will get a grade of John according to his obtained mark. Since we are applying the formula in a table, we don’t have to use the Fill Handle tool to copy it.

Consequently, you will get all the grades of the students by looking them up from the grading system table. And, for example, the result would look like this.


Formula 2: Using VLOOKUP Function

Now, suppose you have the grade list of the students. Now, you want to find a particular student’s grade from the list. You can do this easily with the VLOOKUP function.  Go through the steps below to do this.

📌 Steps:

  • At the very beginning, automate the students named for quick and easy use.
  • To do this, click on the F13 cell >> go to the Data tab >> Data Tools group >> Data Validation tool >> Data Validation… option.

Access the Data Validation Tool

  • As a result, the Data Validation window will appear.
  • Subsequently, go to the Settings tab of the window.
  • Afterward, choose the List option from the Allow: dropdown menu.
  • Subsequently, reference to the B5:B14 cells of the Sample Dataset in the Source: text box.
  • Finally, click on the OK button.

Finalize the Data Validation

  • As a result, the data validation is done. And, you will see every student’s name in the dropdown list at the F13 cell.

  • Now, say we chose Henry to find his grade by looking up the grade list.
  • Afterward, click on the G13 cell and insert the following formula.
=VLOOKUP(F13,$B$5:$D$14,3,FALSE)
  • Subsequently, hit the Enter button.

As a result, your required task is accomplished. Now, you can see you have individual students’ grades and you can lookup any student grade from the table of grade list of students.


Formula 3: Combining INDEX and MATCH Functions to Lookup Value in a Table

Another thing, you can do to lookup a value in a table is to combine the INDEX and MATCH functions. Follow the steps below to do this.

📌 Steps:

  • Initially, make the list of students’ names at the F13 cell with data validation following the first 8 steps of the previous method.
  • Afterward, click on the G13 cell and insert the following formula.
=INDEX($D$5:$D$14,MATCH(F13,$B$5:$B$14,0))
  • Subsequently, press the Enter button.

Combining INDEX and MATCH Functions to Lookup Value in a Table

As a result, you can lookup any student’s grade number from their grade list table.


Conclusion

In a nutshell, in this article, I have shown you 3 effective Excel formulas to lookup a value in a table. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are very welcome to comment here if you have any further questions or recommendations.

And, visit ExcelDemy to learn about many more Excel problem solutions, tips, and tricks. Thank you!

Tanjim Reza

Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo