# Comparing columns and returning value

Solved/Closed
Related:

- Compare 2 text cells and show difference in the third cell
- Compare three columns in excel and return a value - Best answers
- What function would you use to search for a certain value in a spreadsheet column to return the corresponding piece of informati - Best answers
- Compare values in two columns and return the value from third ✓ - Forum - Excel
- Compare 2 excel files and highlight differences ✓ - Forum - Excel
- Compare 2 Sheets -> Summary differences ✓ - Forum - Excel
- Macro to compare 2 sheets and copy differences ✓ - Forum - Excel
- Help: Need formula to find missing text comparing 2 cells ✓ - Forum - Excel

## 12 replies

Excelguru

May 28, 2009 at 02:27 AM

- Posts
- 261
- Registration date
- Saturday April 11, 2009
- Status
- Member
- Last seen
- June 21, 2011

May 28, 2009 at 02:27 AM

Hi

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

Do let me know If it helps...

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

Do let me know If it helps...

Excelguru

May 28, 2009 at 02:26 AM

- Posts
- 261
- Registration date
- Saturday April 11, 2009
- Status
- Member
- Last seen
- June 21, 2011

May 28, 2009 at 02:26 AM

Hi

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

Do let me know If it helps...

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

Do let me know If it helps...

mubashir aziz

May 28, 2009 at 02:07 AM

- Posts
- 190
- Registration date
- Sunday April 12, 2009
- Status
- Member
- Last seen
- February 16, 2010

May 28, 2009 at 02:07 AM

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)

D1==CHAR(91-A1)

C1==CHAR(91-b1)

Hi,

I've been driving myself crazy on excel trying to figure out a formula to calculate the responses for a test. Here's what I need it to do:

If the value in cell A2 matches the value in cell B2, I need it to display a 1.

If the value in cell A2 does NOT match the value in cell B2 and is not blank, I need it to subtract .25 or display -.25 (whichever).

If there is no value in cell A2 (if it's blank), I need it to display 0.

I can't find this anywhere because nothing explains how to pick between 3 inputs and then return one of 3 values.

Hope you can help.

I've been driving myself crazy on excel trying to figure out a formula to calculate the responses for a test. Here's what I need it to do:

If the value in cell A2 matches the value in cell B2, I need it to display a 1.

If the value in cell A2 does NOT match the value in cell B2 and is not blank, I need it to subtract .25 or display -.25 (whichever).

If there is no value in cell A2 (if it's blank), I need it to display 0.

I can't find this anywhere because nothing explains how to pick between 3 inputs and then return one of 3 values.

Hope you can help.

rizvisa1

Aug 7, 2010 at 07:15 PM

- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022

Aug 7, 2010 at 07:15 PM

Seems to be a totally different question. It is better if you start a new thread

Didn't find the answer you are looking for?

Ask a question
Excelguru

May 28, 2009 at 02:25 AM

- Posts
- 261
- Registration date
- Saturday April 11, 2009
- Status
- Member
- Last seen
- June 21, 2011

May 28, 2009 at 02:25 AM

Hi

Use =VLOOKUP(A1,$B$1:$C$9,2,FALSE)

in cell D1 and copy downwards

Do let me know If it helps...

Use =VLOOKUP(A1,$B$1:$C$9,2,FALSE)

in cell D1 and copy downwards

Do let me know If it helps...

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.

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.

mubashir aziz

Jun 10, 2009 at 10:27 PM

- Posts
- 190
- Registration date
- Sunday April 12, 2009
- Status
- Member
- Last seen
- February 16, 2010

Jun 10, 2009 at 10:27 PM

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)

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

Hello. Here is my scenario/problem.

I want a formula that will populate column D with the results of the following. It will find anything in column A that also appears in column B. If it appears in column B it will display the associated value from column C.

Make sense? Please help...

I want a formula that will populate column D with the results of the following. It will find anything in column A that also appears in column B. If it appears in column B it will display the associated value from column C.

Make sense? Please help...

rizvisa1

Mar 12, 2010 at 04:53 AM

- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022

Mar 12, 2010 at 04:53 AM

I think you need an IF statement

=IF(condition, true action, false action)

Lets say we are working in row 2

then in D2, you can write

=IF(A2=B2, C2, "")

Which says that if a2 is same as b2, then show value of c2 else just show blank ("")

=IF(condition, true action, false action)

Lets say we are working in row 2

then in D2, you can write

=IF(A2=B2, C2, "")

Which says that if a2 is same as b2, then show value of c2 else just show blank ("")

Hello all,

Assume that in A1:C5 I have the following table:

A B C

1 1 Low

2 1 Low Mod

3 1 Moderate

4 1 Mod High

5 1 High

And I have in Row 10 the following:

Row 10: A10=3 and B10=1

What sort of formula do I need to create in C10 that would look up the value from the table above (A1:C5) and would match the value of A10 to the relevant value in column A, and does the same for the value in B10 to the relevant value in column B so that the outcome is the corresponding value in column C... C3 = "Moderate"

Your assistance is highly appreciated.

Assume that in A1:C5 I have the following table:

A B C

1 1 Low

2 1 Low Mod

3 1 Moderate

4 1 Mod High

5 1 High

And I have in Row 10 the following:

Row 10: A10=3 and B10=1

What sort of formula do I need to create in C10 that would look up the value from the table above (A1:C5) and would match the value of A10 to the relevant value in column A, and does the same for the value in B10 to the relevant value in column B so that the outcome is the corresponding value in column C... C3 = "Moderate"

Your assistance is highly appreciated.

rizvisa1

Aug 23, 2010 at 02:04 PM

- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022

Aug 23, 2010 at 02:04 PM

=INDIRECT("C" & SUMPRODUCT((A1:A6=A10) * (B1:B6=B10) * ROW(B1:B6)))

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.

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.

mubashir aziz

Sep 4, 2009 at 01:17 AM

- Posts
- 190
- Registration date
- Sunday April 12, 2009
- Status
- Member
- Last seen
- February 16, 2010

Sep 4, 2009 at 01:17 AM

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","")

shakeel ahmed
>
mubashir aziz

Nov 12, 2009 at 10:50 AM

- Posts
- 190
- Registration date
- Sunday April 12, 2009
- Status
- Member
- Last seen
- February 16, 2010

Nov 12, 2009 at 10:50 AM

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

=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

mubashir aziz

May 28, 2009 at 01:46 AM

- Posts
- 190
- Registration date
- Sunday April 12, 2009
- Status
- Member
- Last seen
- February 16, 2010

May 28, 2009 at 01:46 AM

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

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

mubashir aziz

May 28, 2009 at 05:10 AM

- Posts
- 190
- Registration date
- Sunday April 12, 2009
- Status
- Member
- Last seen
- February 16, 2010

May 28, 2009 at 05:10 AM

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

D1=CHAR(91-A1) and drag it down ......

mubashir aziz

May 31, 2009 at 10:07 PM

- Posts
- 190
- Registration date
- Sunday April 12, 2009
- Status
- Member
- Last seen
- February 16, 2010

May 31, 2009 at 10:07 PM

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.

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.

Hi all,

Is there a way to compare the contents of a cell with a different cell using TRUE/FALSE results?

For instance,

cell A1 contains: Jr3

comparing with

cell B1 contains: Jr3

cell C1 (the result cell) will show up TRUE

now what if I want to match the content of B1 with cell A1 which contains more than one value in it?

For instance,

cell A1: Jr3, Jr4

comparing with

cell B1 containing Jr3

cell C1 should show up TRUE (since cell A1 does have Jr3 as its value separated by a comma)

Please Help

Is there a way to compare the contents of a cell with a different cell using TRUE/FALSE results?

For instance,

cell A1 contains: Jr3

comparing with

cell B1 contains: Jr3

cell C1 (the result cell) will show up TRUE

now what if I want to match the content of B1 with cell A1 which contains more than one value in it?

For instance,

cell A1: Jr3, Jr4

comparing with

cell B1 containing Jr3

cell C1 should show up TRUE (since cell A1 does have Jr3 as its value separated by a comma)

Please Help

rizvisa1

Sep 1, 2010 at 06:35 PM

- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022

Sep 1, 2010 at 06:35 PM

You can use find to do that

May 29, 2009 at 01:15 PM

Thanks!

Apr 26, 2010 at 03:52 AM

Keep up the good work

Jul 19, 2013 at 12:14 PM

Oct 10, 2013 at 10:39 AM

Nov 7, 2013 at 12:22 AM