Ask a question »

Excel - Comparing columns and returning value

September 2015

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.

[Excel] - comparing columns and returning value



Issue



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

EX:      
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.

Solution


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.
D1==CHAR(91-A1)

C1==CHAR(91-b1)
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.

=VLOOKUP(A1,$B$1:$B$6,1,FALSE) 

Note


Thanks to mubashir aziz for this tip on the forum.
For unlimited offline reading, you can download this article for free in PDF format:
Excel-comparing-columns-and-returning-value.pdf

See also

In the same category

Published by aakai1056.
This document entitled « Excel - Comparing columns and returning value » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.