Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

If One Cell Equals Another Then Return Another Cell in Excel

MS Excel provides various options and methods to make our tasks easier. In this article, I will show some ways to check if one cell equals another and then return another cell in Excel.


Download Practice Workbook

You can download the workbook to practice yourself.


5 Ways to Check If One Cell Equals Another & Then Return Another Cell in Excel

Here, we will show you 5 different ways to check if one cell equals another and then return another cell in Excel using different functions.


1. Use IF Function to Check If One Cell Equals Another & Returns

The IF function is one of the simplest functions which is used to do a logical comparison between two values. In this method, we will see how to use the IF function to compare one cell with another and return another cell value. Before going to the example let’s know more about this function. The syntax of the function is like this:

=IF(logical_Condition, [value_if_true], [value_if_false])

In the first portion of the parameter, we need to pass our condition based on which we are going to compare. Then the second and third part defines what will be if the values after comparison get True or False.


1.1 Returning Exact Value of Cell

Assuming, we have a dataset of some Fruits with two columns. Every row has a specific Value. Now we will find the rows where Fruits 1 and Fruits 2 are matched and display their values in the Matched Values column.

Ways to Check If One Cell Equals Another & Then Return Another Cell in Excel

To do that, go through the steps given below.

Steps:

  • Firstly, enter the below formula in Cell D4.
=IF(B5=C5,D5,"")

Check If One Cell Equals Another & Then Return Another Cell in Excel Using IF Function

  • Now, press Enter.
  • Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

Here, in the IF function, using the condition B5=C5 we are comparing the Fruits Name of each column Fruits 1 and Fruits 2. If the condition gets True, then it will print the values from the Value column into Matched Values column.
  • Thus, you can return a specific Cell value if one cell equals another cell.

Read More: How to Select Cells with Certain Value in Excel (5 Methods)


1.2 Updating Resultant Value

In this method, we will use the same IF function, and depending on the condition we will use a formula and show them in another cell. Let’s think about the same dataset used in the previous method but here I will update the new Price if the Flag value is not “X” and our new price will be 2 times the current price.

Steps:

  • In the beginning, enter the following formula in Cell E5.
=IF(D5<>"X",C5*2,C5)

Check If One Cell Equals Another & Then Return Another Cell in Excel Using IF Function with Update

  • After that, press Enter and copy down the formula up to Cell E9.

In this formula, using D5<>” X” we are checking if the Flag value is not equal to “X” or not. If the condition is True then it will double the price otherwise it will remain the same.
  • Finally, you will get all the required resultant values updated.

Excel If One Cell Equals Another Then Return Another Cell

Read More: How to Lock Cells in Excel Formula (2 Easy Ways)


2. Return Another Cell Value Using VLOOKUP Function

In terms of searching for something in Excel, the LOOKUP function will be the proper choice for that. This function allows us to search vertically or horizontally within a condition in a certain range. For those specific purposes, there are VLOOKUP and HLOOKUP functions in Excel. Let’s see the fundamentals of the VLOOKUP function. The syntax of the function is like this:

=VLOOKUP (value, table, col_index, [range_lookup])

Firstly, the value -> carries the value to look for in the first column of a table.

table -> Here will be the table name.

col_index -> It is the column index value of the table from where we will collect a value.

[range_lookup] -> This last section is for denoting the optional range.

For example, consider a dataset of some Fruits like before. But here we will have 3 columns which are Fruits, ID, Price. Now we will search Fruits’ prices from this table using VLOOKUP.

Check If One Cell Equals Another & Then Return Another Cell Using VLOOKUP Function

Steps:

  • Firstly, enter the following formula in Cell G4.
=VLOOKUP(G4,B4:D9,3,0)

  • Next, press Enter.
  • Thus, you can find any other Fruit’s price by entering the Name on Cell G4.

Here in the function, first I have passed the value which is Cell G4, then the table from where we want to extract data which is indicated by cell range B4:D9 the whole table. After that in the third part, we will get the values from the Price column which is column no 3 that’s why we need to pass 3. Lastly, 0 is used to specify that we want an exact match.

3. Apply Excel HLOOKUP Function to Scan Matching Value

Now we will see the uses of HLOOKUP functions if our data is horizontally designed. The syntax of the HLOOKUP functions is:

=HLOOKUP (lookup_value, table_array, row_index, [range_lookup])

It is almost like the VLOOKUP function. The only difference is instead of having a column index here is the row index in the 3rd part of the parameter.

Steps:

  • In the beginning, select Cell C9 and insert the following formula.
=HLOOKUP(C8,B4:G6,3,0)

Check If One Cell Equals Another & Then Return Another Cell Applying Excel HLOOKUP Function

  • Then, press Enter.
  • Finally, we can see the result.

As we have discussed earlier it is almost like VLOOKUP. Here I have passed the row-wise value instead of column-wise. That’s why first we entered the row index of our desired value which is Cell C8. Besides, the table range also changes as our table is shifted horizontally.

Read More: How to Display the Cell Formulas in Excel (6 Methods)


Similar Readings


4. Check If One Cell Equals Another with INDEX & MATCH Functions

In this section, we will do the same thing done by the LOOKUP function, but the only difference is here we will not use the LOOKUP function. INDEX and MATCH functions will do the same thing as LOOKUP. Also, the dataset will be the same as well. Before going to the example let’s see the details about these two functions.

=INDEX (array, row_number, [col_number], [area_number])

This function can take a maximum of four arguments and a minimum of two arguments. In the first section of its parameter, it takes the range of cells from where we will check the index value. Then comes the row number of reference or matching value. The last two arguments are optional with them we can define or specify the column number from where the matched data will be retrieved and the area range number.

=MATCH (lookup_value, lookup_array, [match_type])

Another mostly used function is the MATCH function. The first argument takes the lookup value or the value we are going to match. The second one is the array or range where we will search our desired data. And the last one is the match type. Depending on different match type values we can control matching.

1 -> By declaring 1 it will match or find the largest value less than or equal to the lookup value.

0 -> If we put 0 as a match type it will match the value which is exactly as the lookup value.

-1 -> This will match the smallest value greater than or equal to the lookup value.

Follow the steps given below to use these two functions.

Steps:

  • Firstly, enter the formula in Cell G5.
=INDEX(B4:D9,MATCH(G4,B4:B9,0),3)

Check If One Cell Equals Another then Return Another Cell with INDEX & MATCH Functions in Excel

  • Secondly, press Enter.
  • In the end, we can see the final result.

In the MATCH function, we tried to match the value which is in Cell G4 from the cell range B4:B9 in our lookup table. And as we considered the exact match that’s why 0 is assigned at the last argument. Then, the outer function is the INDEX function. In the first part, we have assigned the cell range B4:D9. Then the matched value will be calculated from the MATCH function. Lastly, 3 is used as we want to get data from the third column of our lookup table.

5. Return Items from Another Worksheet with Matching Value in Excel

Let’s have two worksheets, one is Weekly Meals, and another is Ingredients. Now I will show how to compare meals and show the ingredients in the first worksheet. The Week Meals Planning worksheet will be like this:

Week Meals Planning Worksheet

And the meals ingredients worksheet will be like this:

Meals Ingredients Worksheet

Now, I will show how to find the food ingredients from the ingredient worksheet to the Meal worksheet by entering the name of the food in Cell B14.

Steps:

  • To start with, enter the formula in Cell C14.
=VLOOKUP($B14,ingredients!$B$5:$E$16,COLUMN()-1,FALSE)

  • After that, press Enter.
  • Then, copy the formula to the right side.

Return Items from Another Worksheet If One Cell Equals Another in Excel

In the formula, we have passed the lookup value as Cell $B14, then the other worksheets (ingredients worksheet) cell range $B$5:$E$16 is sent. After that, we passed the COLUMN function to get the column value of that row. Lastly, we used FALSE to find an exact match, which means it is case sensitive and by declaring false it will search for the exact value matching.
  • Finally, all the ingredients of the selected Food will be displayed.

You can check by typing any food name in Cell B14 and pressing Enter. Same way if just type any food item under Food Name, it will show all the ingredients of that selected item from another worksheet.


Practice Section

In the article, you will find an Excel workbook like the image given below to practice on your own.

Practice Section


Conclusion

These are the ways to check if one cell equals another and then return another cell in Excel. I have shown all the methods with their respective examples. Also, I have discussed the fundamentals of this function and the most used format codes of this function. If you have any other method of achieving this, then please feel free to share it with us.


Related Articles

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo