Tanjim Reza
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.
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.
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.
1. Using Excel LOOKUP Function to Lookup Value in Table
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.
2. Using Excel VLOOKUP Function to Lookup Value
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.
- 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.
- 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.
Read More: Applications of Absolute Structured References in Excel Table Formulas
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.
As a result, you can lookup any student’s grade number from their grade list table.
Download Practice Workbook
You can download our practice workbook from here for free!
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!
