Comparing columns and returning value [Solved/Closed]

Report
Posts
5
Registration date
Sunday February 17, 2013
Status
Member
Last seen
February 19, 2013
-
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
-
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 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



If column A has got a value and other columns are blank then answer in column D should be 140*1.
If column B has got a value and columns from C is blanks then answer in column D should be 140*2 and so on

5 replies

Posts
5
Registration date
Sunday February 17, 2013
Status
Member
Last seen
February 19, 2013
1
Hi

Actully, I need to compare 12 columns.(12 months of an year). With IF statement I can use only 7 'Ifs' and the ramining 5 columns remains without comparing.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
So in your sample, the multiple that you are using with 140 refers the month ?

Can you have more than one column Filled in. If yes then then what should happen ?
Posts
5
Registration date
Sunday February 17, 2013
Status
Member
Last seen
February 19, 2013
1
Hi

You are right. The multiple I use with 140 refers to month.

I have data in more than one column.

I want if column Jan has some value answer should be 140*1
If column Feb has some value answer should be 140*2 and so on until Dec.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
what if both columns jan and has data ?

does the header of the column shows date or month?
Posts
5
Registration date
Sunday February 17, 2013
Status
Member
Last seen
February 19, 2013
1
If both columns has data, the answer should be last column *140.

For eg: If feb has a data 140*2
If mar has a data 140*3 and so on

the column header shows month
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
I am sure I am not 100% following you

but lets say your dates start from Column A till Column L
and you want the result in column N
and there are months like JANUARY etc in row 1

then you can try some thing like this

=140 * MONTH("01-" & INDEX(A1:L1,MATCH(9.99999999999999E+307,L2:L2)) & "-2013")
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
look into IF statement
Hi is there a formula if i want to look for the false in value the result would be the column title of the false value
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
Hi Arlene,

Not sure I understand your question?

You want to replace all the cells containing "False" with its corresponding column header?

Can you give an example and also mention to what range it applies?

Best regards,
Trowa
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
9
Hi Thakkudu

For your data in post #1, the following formula should cover that. In D1 & copy down.

=IF(AND(A1>0,COUNTA(B1:C1)=0),140*1,IF(AND(B1>0,C1=""),140*2,""))

Perhaps it would be better if you posted a sample worksheet with dummy data.

Kevin

@ rizvisa1
Are you ever going to post a example of what you suggest!!
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
@ Kevin@Radstock
What is the point of giving an answer if you don't know the full extent of the query?
Your answer is rubbish considering Thakkudu's reply on Feb 17, 2013 11:09PM.
So it's not a bad idea to ask some questions to get a better idea/understanding of what needs to be solved.
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
Hi Thakkudu,

You can use more than 7 if's if you are willing to sacrifise another column.

This formula should be placed where you want the result:
=IF(L2<>"",12*140,IF(K2<>"",11*140,IF(J2<>"",10*140,IF(I2<>"",9*140,IF(H2<>"",8*140,N2)))))

When all of these if statements are false, the formula will look at N2. You can change this to whatever column you want to use. Since you won't be doing anything else with this data the column can be hidden or the text made white.

The formula in N2 (or wherever) will look like:
=IF(G2<>"",7*140,IF(F2<>"",6*140,IF(E2<>"",5*140,IF(D2<>"",4*140,IF(C2<>"",3*140,IF(B2<>"",2*140,IF(A2<>"",1*140,"")))))))

So two formula's working in harmony to give you the desired result.

Best regards,
Trowa
Posts
5
Registration date
Sunday February 17, 2013
Status
Member
Last seen
February 19, 2013
1
Hi Trowa

This worked and is really helpful. I can use this for my other worksheets also.

Thank you sooooooooooooooo much.

regards
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
Glad I could help.