Search : in
By :

Comparing columns and returning value

Last answer on Nov 12, 2009 3:50:15 pm GMT ll, on May 27, 2009 8:08:51 pm BST 
 Report this message to moderators

Hello,

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

Could someone help?

Thanks!
Configuration: Windows XP Internet Explorer 7.0

Best answers for « Comparing columns and returning value » in :
Excel - Comparing columns and returning value Show Excel - Comparing columns and returning value Issue Solution Note 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 ...
Compare column Show
Can vba return excel cell value with formats Show
Excel – Comparing cell A1 to entire A column in Sheet 2 ShowExcel – Comparing cell A1 to entire A column in Sheet 2 Issue Solution Note Issue I have been trying to compare sheet1 A2 to sheet2 A2 through A500 and if it exists somewhere in sheet2's a col then copy that entire row to a new sheet....
Colouring cells on conditions ShowColouring cells on conditions There are many pratical functions under Excel which is not commonly used. Example: If you wish a cell automatically turns red (or other formatting border, frame etc) under one condition: a result, a...
[Bash]Parameters Show[Bash]Parameters Intro Example 1 Example 2 Initialize parameters Examples Example 3 Intro You can provide a script on the command line and arguments also known as parameters for its implementation. There are two categories of...
Download Future Value of Savings Calculator Show
Download SQL Studio Data Compare Show
Download Comparison Chart ShowComparison Chart is a tool for comparing your products in a chart. With this tool, it is possible to have a graphical view of your products and compare the obtained results. It allows you to customize your chart by using texts, background colors,...
Worksheet - Cells ShowThe Concept of a Cell A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also...
Ask your question Reply

1

mubashir aziz, on May 28, 2009 6:46:08 am BST

Although you question is quite confusing because in your title you are asking to compare two column which means you want to compare but in your example you are talking about A & B Separately.

Anyway, Copy below formula in Cell D1 and it will give you the values of 1 to 26 from Z to A.

D1=CHAR(91-A1)

For

C1=CHAR(91-B1)

For any confusion don't hesitate to consult with me.........
Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran

   
Reply to mubashir aziz

2

mubashir aziz, on May 28, 2009 7:07:00 am BST
  • +1

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)
Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran

   
Reply to mubashir aziz

3

Excelguru, on May 28, 2009 7:25:43 am BST
  • +1

Hi

Use =VLOOKUP(A1,$B$1:$C$9,2,FALSE)
in cell D1 and copy downwards

Do let me know If it helps... Winners are losers who got up and gave it one more try. -Den­nis DeYoung
My Interests are financial Modelling and custom excel develo­pment.

   
Reply to Excelguru

10

Natdev, on Jun 11, 2009 1:26:17 am BST
  • +1

Hi,
This is my dilema , i obviously am missing something here

Old record New record

1 1
4 4
3 5
5 6
7 8
8 9

How do i get Column C to give me :
1. If the same values in A and B to show the same in C using False AND
2. If the values are not the same then #N/A in Column C

I seem to be challenged trying to use one vlookup formula to compare all the values of Column A and B to display the result in C.
Please help.

   
Reply to Natdev

11

mubashir aziz, on Jun 11, 2009 3:27:59 am BST
  • +2

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)


Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz

4

Excelguru, on May 28, 2009 7:26:50 am BST
  • +1

Hi

Use in Cell D1 the formula, =VLOOKUP(A1,$B$1:$C$9,2,FALSE)


Do let me know If it helps... Winners are losers who got up and gave it one more try. -Den­nis DeYoung
My Interests are financial Modelling and custom excel develo­pment.

   
Reply to Excelguru

12

bath, on Jul 30, 2009 8:56:35 am BST

A1 418
a2 817
a3 525
a4 635
a5 254
a6 852
a7 969
a8 857
a9 568
a10 963
a11 254

when i put a9 in column A1 , COLUMN D1 should return the value 568, when i put a5 in colum A1, then column Di should return the value 254, like that...can u assist ?

   
Reply to bath

5

Excelguru, on May 28, 2009 7:27:49 am BST

Hi

Use the formula (in cell D1) =VLOOKUP(A1,$B$1:$C$9,2,FALSE)
Do let me know If it helps... Winners are losers who got up and gave it one more try. -Den­nis DeYoung
My Interests are financial Modelling and custom excel develo­pment.

   
Reply to Excelguru

7

ll, on May 29, 2009 6:15:53 pm BST
  • +2

Thanks so much, the VLOOKUP formula worked! I wouldn't have figured it out on my own... I think I understand it, but not sure what the '2' does in the 2,FALSE part of the formula. If you'd like to explain, that'd be great, if not, you've helped enough and saved me hours of work!

Thanks!

   
Reply to ll

6

mubashir aziz, on May 28, 2009 10:10:49 am BST

Put this formula 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) and drag it down ......

Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran

   
Reply to mubashir aziz

8

ll, on May 29, 2009 6:20:22 pm BST

Thanks for the response Mubashir - I used the numbers/alphabet to illustrate my example, I actually have words/amts etc. that i was trying to match up, I should have been clearer on that - good formula to know though!

   
Reply to ll

9

mubashir aziz, on Jun 1, 2009 3:07:38 am BST

This is the syntax of Vlookup .... in your formula 2 is the nth column in Range .... If you will not False in the formula then it will return you closest match and will not return any error ......


VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)­

Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.

The values in the first column of table_array can be text, numbers, or logical values.

Uppercase and lowercase text are equivalent.








Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran

   
Reply to mubashir aziz

13

Jireh, on Aug 13, 2009 12:55:02 am BST

Hello,

Please help me, what is the formula to get the value from column B that is not in column A?

I have compare new data I want to find out what is the new value that has been added to B that is not

in A. I know how to use vlookup basic but it's a bit complicated since it will only show 1 result as well as with

the exact formula it will only show false and I still have to dig in the values one-by one to get the value that

doesn't really have a matching value in the spreadsheet.

   
Reply to Jireh

14

mubashir aziz, on Sep 4, 2009 6:17:03 am BST
  • +3

Suppose you are compaing Col. B with Col A. now write this formula in C1 and drag it down ... hope it will help you ...... 

=IF(ISERROR(VLOOKUP(B1,$A$1:$A$8,1,FALSE)),"New Data","")


Never ashamed to get or give Advise.
Muhammad Mubashir Aziz , Lodhran, Pakistan

   
Reply to mubashir aziz

15

 shakeel ahmed, on Nov 12, 2009 3:50:15 pm GMT

Dear brother

=IF(ISERROR(VLOOKUP(B1,$A$1:$A$8,1,FALSE)),"New Data","")

is a very nice formula which provide u. i use it same u ask and i find correct value which i want.


keep it up to share these types of information

May Allah Help u

Thanks & Regards

Shakee Ahmed

   
Reply to shakeel ahmed
Ask your question Reply
They need your help