Below, we have a fixed grading system for an institution.
And a dataset of 10 students’ marks and grades per the institution’s grading system.
Method 1 – Use Cell Reference to Display Text
1.1 From the Same Worksheet
Steps:
- Tim’s grade is in cell E7 of the active worksheet.
- Click on cell G5 and insert the following formula:
=E7- Press the Enter button.
As a result, you will get the grade of Tim in the G5 cell as per your desire.
Read More: How to Reference Text in Another Cell in Excel
1.2 From Different Worksheets
Steps:
- The remark for Tim’s grade is in cell E8 of the Grading System worksheet.
- Click on cell G8 and insert the following formula:
='Grading System'!E8- Press the Enter button.
You will get the remark for Tim’s grade in your desired cell.
Read More: How to Use Variable Row Number as Cell Reference in Excel
Method 2 – Use Named Range to Display Text from Another Cell
Steps:
- Create a named range for Tim’s grade.
- Click on cell E7 >> go to the Formulas Tab >> Defined Names group >> Define Name option.
- The New Name window will appear.
- Type _tim_grade in the Name: text box and click the OK button.
- Click on cell G5 and insert the formula below:
=_tim_grade- Press the Enter button.
You will get Tim’s grade displayed in the required cell.
Read More: How to Reference Cell by Row and Column Number in Excel
Method 3 – Display Formula Text from Another Cell’s Formula
Steps:
- Click on cell B17.
- Insert the following formula containing the FORMULATEXT function and press the Enter button.
=FORMULATEXT(E5)
The formula for cell E5 will be displayed in cell B17.
Read More: How to Find and Replace Cell Reference in Excel Formula
Method 4 – Use Excel Functions to Display Specific Parts of Text from Another Cell
4.1 Show the Left Part of a Text with the LEFT Function
Steps:
- Click on cell G5.
- Insert the following formula and press the Enter button.
=LEFT(C10,2)
You will see the first 2 letters shown in cell G5.
Read More: How to Use OFFSET for Cell Reference in Excel
4.2 Show the Middle Part of a Text with the MID Function
Steps:
- Click on cell G5.
- Insert the following formula and press the Enter button.
=MID(C10,3,3)
As a result, you will get the 3 middle letters starting from the 3rd letter of cell C10 on cell G5.
4.3 Show the Right Part of a Text with the RIGHT Function
Steps:
- Click on cell G5 and insert the formula below:
=RIGHT(C10,2)- Press the Enter button.
You will get your desired result.
How to Combine and Display Text from Multiple Cells in Excel
Steps:
- Click on cell G5.
- Insert the following formula:
=C5&" - "&D5- Press the Enter button.
You will get your desired result displayed on cell G5.
- Use the CONCATENATE Function
You can also use the CONCATENATE function to accomplish the previous task. Follow the steps below to perform this.
Steps:
- Click on cell G5.
- Insert the following formula:
=CONCATENATE(C5," - ",D5)
- Press the Enter button.
You will see that you have achieved your desired target.
- Use the TEXTJOIN Function
Moreover, you can use the TEXTJOIN function to combine and display text from other cells. Go through the steps below to do this.
Steps:
- Click on cell G5.
- Insert the following formula and press the Enter button.
=TEXTJOIN(" - ",TRUE,C5,D5)
You will get your desired result.
How to Display Particular Text in Excel Based on Values of Other Cells Using Formula
- Use IF Function
Steps:
- Click on cell E5.
- Insert the following formula containing the IF function based on the grading system dataset.
=IF(D5>=90,"O",IF(D5>=80,"A+",IF(D5>=70,"A",IF(D5>=60,"B+",IF(D5>=50,"B",IF(D5>=40,"C","F"))))))- Press the Enter button.
You can use the fill handle feature to get the grades for all students based on the grading system dataset.
- Use INDEX-MATCH Formula
You can find a particular student’s grade from the student report card dataset by using the combination of INDEX and MATCH functions. Follow the steps below to do this. Say you want to find the grade of the student whose name is on cell D16.
Steps:
- Click on cell D17.
- Insert the formula below:
=INDEX(C5:E14,MATCH(D16,C5:C14,0),3)- Press the Enter button.
You will find Tim’s grade from the report card dataset.
Download the Practice Workbook
You can download our practice workbook from here for free!
Related Articles
- How to Use Cell Value as Worksheet Name in Formula Reference in Excel
- How to Reference a Cell from a Different Worksheet in Excel
- How to Reference Cell in Another Sheet Dynamically in Excel
- Excel VBA Examples with Cell Reference by Row and Column Number
- Excel VBA: Cell Reference in Another Sheet
<< Go Back to Cell Reference in Excel | Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
