Comparing columns and returning value

Solved/Closed
Thakkudu Posts 5 Registration date Sunday February 17, 2013 Status Member Last seen February 19, 2013 - Feb 17, 2013 at 07:00 AM
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 - Aug 20, 2015 at 11:03 AM
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

Thakkudu Posts 5 Registration date Sunday February 17, 2013 Status Member Last seen February 19, 2013 1
Feb 17, 2013 at 11:09 PM
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
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Feb 18, 2013 at 06:36 AM
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 ?
0
Thakkudu Posts 5 Registration date Sunday February 17, 2013 Status Member Last seen February 19, 2013 1
Feb 18, 2013 at 07:05 AM
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.
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Feb 18, 2013 at 07:28 AM
what if both columns jan and has data ?

does the header of the column shows date or month?
0
Thakkudu Posts 5 Registration date Sunday February 17, 2013 Status Member Last seen February 19, 2013 1
Feb 18, 2013 at 07:33 AM
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
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Feb 18, 2013 at 08:09 AM
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")
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Feb 17, 2013 at 08:44 AM
look into IF statement
0
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
0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Aug 20, 2015 at 11:03 AM
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
0
Kevin@Radstock Posts 42 Registration date Thursday January 31, 2013 Status Member Last seen April 26, 2014 9
Feb 18, 2013 at 02:02 AM
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!!
0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Feb 18, 2013 at 10:57 AM
@ 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.
0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Feb 18, 2013 at 10:49 AM
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
0

Didn't find the answer you are looking for?

Ask a question
Thakkudu Posts 5 Registration date Sunday February 17, 2013 Status Member Last seen February 19, 2013 1
Feb 19, 2013 at 12:48 AM
Hi Trowa

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

Thank you sooooooooooooooo much.

regards
0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Feb 19, 2013 at 10:42 AM
Glad I could help.
0