Excel - Comparing columns and returning value

March 2017

Microsoft Excel office software is a very useful tool for comparison-based calculations. The office software simplifies complicated and time-consuming methods of manual comparison to get the desired results without any errors. You may require the values from two adjacent columns to be compared and the results to be displayed in the third adjoining column. The fourth column will display the corresponding alphabet in the reverse order for the number displayed in the first column. For example, if in the first column the number 1 is displayed then in the last column the letter will be a Z. The formula in Excel that can make it all much simpler is =CHAR(91-A1) in the D1 cell; drag it down by pressing the cursor, to make the formula applicable to all the cells below.


I have an Excel spreadsheet where I want to compare values between two columns and return the value from another column.

Column: A     B     C     D      
        1     1     Z      Formula: return value      
        1     2     Y       
        1     3     X      
        1     4     W      
        2     5     V      
        2     6     U      
        2     7     T      
        3     8     S      
        3     9     R 

What I would like to happen in column D is for it to look at what is in column A, find the matching value in column B, and return the value listed in C. i.e. for any value of '1' in column A, I would want column D to display 'Z', for any value of '2' in column A, I would want column D to display 'Y', for any value of '3' in column A, I would want column D to display 'X', etc.


Put this formula =CHAR(91-A1) in Cell D1 and drag it down. It will give you Z to A for the values of 1 to 26.

Suppose your data is in Row 1 then use this formula and drag it down, it will see A1 values in whole column of B and if find then give you the value.



Thanks to mubashir aziz for this tip on the forum.


Published by aakai1056.
This document, titled "Excel - Comparing columns and returning value," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).