Formula Needed

[baggarwal]

Hi All:

I need a formula that will insert a number if there is a match from two columns. Here is an example data set:

F E 36.2
F F 36.8
F F 37.1
R E 38.2
F E 27.2
F E 26.5


I need a formula that will look for F in the first colum and F in the second column and then put in a number that is rounded to zero decimal places and if no match is found it will insert a blank. The desired result is:

blank
37
37
Blank
Blank
Blank

Can someone please help me setup a formula.

 

[MrKowz]

Something like this?

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">F</td><td style=";">E</td><td style="text-align: right;;">36.2</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">F</td><td style=";">F</td><td style="text-align: right;;">36.8</td><td style="text-align: right;;">37</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">F</td><td style=";">F</td><td style="text-align: right;;">37.1</td><td style="text-align: right;;">37</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">R</td><td style=";">E</td><td style="text-align: right;;">38.2</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">F</td><td style=";">E</td><td style="text-align: right;;">27.2</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">F</td><td style=";">E</td><td style="text-align: right;;">26.5</td><td style=";"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D1</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">A1:B1,"F"</font>)=2,ROUND(<font color="Red">C1,0</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

 

[baggarwal]

Actually I need a condition for each column as i want to be able to change the F to an E and still get a match. Can you show me a formula that will look for F in A and F in B.

 

[Rick Rothstein]

I think this formula will do what you want (provided neither Column A nor Column B can contain the letters "FF" while the other column is blank)...

=IF(A1&B1="FF",ROUND(C1,0),"")

EDIT
In response to your reply to MrKowz, the first "F" in "FF" refers to Column A's cell value and the second "F" to Column B's... you can change them as needed.

 

[baggarwal]

Actually i need a formula where I could change it so that you will look for R in column A and F in column 2 and then put in the rounded number. Can you tell me a formula that would be able to do this.

 

[Rick Rothstein]

Actually i need a formula where I could change it so that you will look for R in column A and F in column 2 and then put in the rounded number. Can you tell me a formula that would be able to do this.

Reread my message again... I put an EDIT section in that addresses this (your posting and my EDIT crossed during entry).

 

[baggarwal]

Okay let me position it another way. Lets say I want to set the condition to look for an R in Column A and an F in column B. How would I rework your formula to get this. Right now your formula works because it is look for an FF in both columns.

 

[Rick Rothstein]

Okay let me position it another way. Lets say I want to set the condition to look for an R in Column A and an F in column B. How would I rework your formula to get this. Right now your formula works because it is look for an FF in both columns.

In the formula...

=IF(A1&B1="FF",ROUND(C1,0),"")

notice the "FF"... the first character corresponds to the letter in Column A and the second character to the letter in Column B. So, if you want to test for an R in Column A and an F in Column B, the formula would become...

=IF(A1&B1="RF",ROUND(C1,0),"")

All the A1&B1 is doing is concatenating (placing next to each other) the text in Column A's cell with the text in Column B's cell (in that order), so the IF test is looking to see if those concatenated cell's text is equal to "FF" or "RF" or "RE" or whatever you want to test it to be.

To give one more example, if you wanted to test for an R in Column A and an E in Column B, the formula would be...

=IF(A1&B1="RE",ROUND(C1,0),"")

 

[MrKowz]

Try:


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="background-color: #FFFFFF;;">F</td><td style="background-color: #FFFFFF;;">E</td><td style="text-align: right;background-color: #FFFFFF;;">36.2</td><td style="border-right: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="background-color: #FFFFFF;;">F</td><td style="background-color: #FFFFFF;;">F</td><td style="text-align: right;background-color: #FFFFFF;;">36.8</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">37</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="background-color: #FFFFFF;;">F</td><td style="background-color: #FFFFFF;;">F</td><td style="text-align: right;background-color: #FFFFFF;;">37.1</td><td style="text-align: right;border-right: 1px solid black;background-color: #FFFFFF;;">37</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="background-color: #FFFFFF;;">R</td><td style="background-color: #FFFFFF;;">E</td><td style="text-align: right;background-color: #FFFFFF;;">38.2</td><td style="border-right: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="background-color: #FFFFFF;;">F</td><td style="background-color: #FFFFFF;;">E</td><td style="text-align: right;background-color: #FFFFFF;;">27.2</td><td style="border-right: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-bottom: 1px solid black;background-color: #FFFFFF;;">F</td><td style="border-bottom: 1px solid black;background-color: #FFFFFF;;">E</td><td style="text-align: right;border-bottom: 1px solid black;background-color: #FFFFFF;;">26.5</td><td style="border-right: 1px solid black;background-color: #FFFFFF;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D1</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">A1="F",B1="F"</font>),ROUND(<font color="Red">C1,0</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

 

[scottylad2]

either of those solutions work, but you could also refer to cells IF(A1&B1="RE",ROUND(C1,0),"")would adapt to IF(A1&B1=A2&B2,ROUND(C1,0),"")
and place whatever values you want as a criteria in cells A2 and B2 similar can be done with Mr Kowz solution