Previous Topic Next Topic

Comparing columns and returning value

Solved/Closed
Thakkudu Posts 5 Registration date Sunday 17 February 2013 Status Member Last seen 19 February 2013 - 17 Feb 2013 à 07:00
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 20 Aug 2015 à 11:03
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 responses

Réponse 1 / 5
Thakkudu Posts 5 Registration date Sunday 17 February 2013 Status Member Last seen 19 February 2013 1
17 Feb 2013 à 23:09
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.
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
18 Feb 2013 à 06:36
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 ?
Thakkudu Posts 5 Registration date Sunday 17 February 2013 Status Member Last seen 19 February 2013 1
18 Feb 2013 à 07:05
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.
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
18 Feb 2013 à 07:28
what if both columns jan and has data ?

does the header of the column shows date or month?
Thakkudu Posts 5 Registration date Sunday 17 February 2013 Status Member Last seen 19 February 2013 1
18 Feb 2013 à 07:33
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
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
18 Feb 2013 à 08:09
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")
Réponse 2 / 5
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
17 Feb 2013 à 08:44
look into IF statement
Arlene
19 Aug 2015 à 04:18
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
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
20 Aug 2015 à 11:03
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
Réponse 3 / 5
Kevin@Radstock Posts 42 Registration date Thursday 31 January 2013 Status Member Last seen 26 April 2014 9
18 Feb 2013 à 02:02
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!!
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
18 Feb 2013 à 10:57
@ 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.
Réponse 4 / 5
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
18 Feb 2013 à 10:49
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
Réponse 5 / 5
Thakkudu Posts 5 Registration date Sunday 17 February 2013 Status Member Last seen 19 February 2013 1
19 Feb 2013 à 00:48
Hi Trowa

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

Thank you sooooooooooooooo much.

regards
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
19 Feb 2013 à 10:42
Glad I could help.

Similar discussions

Compare values in two columns and return the value from third
ramdubai -
keshav -

11 responses
Comparing columns and returning value
ll -
kittu -

35 responses
Looking For a Value in a Cell
Tom9o -
rizvisa1 -

3 responses
Fill adjacent cell with number from text answer
MitchP -
vcoolio -

20 responses