Formula to add value to cell depending upon criteria in another cell

W

WednesdayC

Board Regular
Joined
Nov 7, 2010
Messages
201
Office Version
  1. 2016
Platform
  1. MacOS
Hi All

Can anyone help me please with a formula for the following example. Ideally, I would like a regular formula, but if this is not possible, then a UDF would be much appreciated.

I have the following in columns A, B and I would like the result to be as per column C (Owner Name) :-

[TABLE="width: 325"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]MEMBER NAME
[/TD]
[TD]STATUS[/TD]
[TD]OWNER NAME[/TD]
[/TR]
[TR]
[TD]JOHN DOE
[/TD]
[TD]OWNER[/TD]
[TD]JOHN DOE[/TD]
[/TR]
[TR]
[TD]JANE DOE
[/TD]
[TD]SPOUSE[/TD]
[TD]JOHN DOE[/TD]
[/TR]
[TR]
[TD]JAMES DOE
[/TD]
[TD]CHILD[/TD]
[TD]JOHN DOE[/TD]
[/TR]
[TR]
[TD]JANICE DOE
[/TD]
[TD]CHILD[/TD]
[TD]JOHN DOE
[/TD]
[/TR]
[TR]
[TD]JACK DOE
[/TD]
[TD]OWNER[/TD]
[TD]JACK DOE[/TD]
[/TR]
[TR]
[TD]JENNY DOE
[/TD]
[TD]SPOUSE[/TD]
[TD]JACK DOE[/TD]
[/TR]
[TR]
[TD]JEFFREY DOE
[/TD]
[TD]OWNER[/TD]
[TD]JEFFREY DOE[/TD]
[/TR]
[TR]
[TD]JOHN DOE
[/TD]
[TD]CHILD[/TD]
[TD]JEFFREY DOE[/TD]
[/TR]
[TR]
[TD]JACK DOE
[/TD]
[TD]OWNER[/TD]
[TD]JACK DOE[/TD]
[/TR]
[TR]
[TD]JANET DOE
[/TD]
[TD]OWNER[/TD]
[TD]JANET DOE[/TD]
[/TR]
</tbody>[/TABLE]

So if there are several family members, only the owners name appears in Column C.

This is beyond me, so I would be very grateful for any workable solution.

Regards

Wednesday
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
How about this.
<table valign="top" border="1"><caption>LEGO HTML</caption><col width="54"><col width="153"><col width="117"><col width="149">
<tr><td></td><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">A</font></th><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">B</font></th><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">C</font></th></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">1</font></th><td style="background-color:rgb(217, 217, 217)"><font face="Calibri" style="color:rgb(0, 0, 0)"><b>MEMBER NAME</b></font></td><td style="background-color:rgb(217, 217, 217)"><font face="Calibri" style="color:rgb(0, 0, 0)"><b>STATUS</b></font></td><td style="background-color:rgb(217, 217, 217)"><font face="Calibri" style="color:rgb(0, 0, 0)"><b>OWNER NAME</b></font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">2</font></th><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">JOHN DOE</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">OWNER</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">JOHN DOE</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">3</font></th><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">JANE DOE</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">SPOUSE</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">JOHN DOE</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">4</font></th><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">JAMES DOE</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">CHILD</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">JOHN DOE</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">5</font></th><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">JANICE DOE</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">CHILD</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">JOHN DOE</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">6</font></th><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">JACK DOE</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">OWNER</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">JACK DOE</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">7</font></th><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">JENNY DOE</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">SPOUSE</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">JACK DOE</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">8</font></th><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">JEFFREY DOE</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">OWNER</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">JEFFREY DOE</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">9</font></th><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">JOHN DOE</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">CHILD</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">JEFFREY DOE</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">10</font></th><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">JACK DOE</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">OWNER</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">JACK DOE</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">11</font></th><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">JANET DOE</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">OWNER</font></td><td style="background-color:rgb(255, 255, 255)"><font face="Calibri" style="color:rgb(0, 0, 0)">JANET DOE</font></td></tr></table><table style="width:100%" valign="top" border="1"><caption>Worksheet Formulas</caption><tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">Cell</font></th><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">Formula</font></th></tr><tr><th style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">C2</font></th></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">=IF(B2="OWNER",A2,C1)</font></td></tr><tr><th style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">C3</font></th></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">=IF(B3="OWNER",A3,C2)</font></td></tr><tr><th style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">C4</font></th></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">=IF(B4="OWNER",A4,C3)</font></td></tr><tr><th style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">C5</font></th></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">=IF(B5="OWNER",A5,C4)</font></td></tr><tr><th style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">C6</font></th></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">=IF(B6="OWNER",A6,C5)</font></td></tr><tr><th style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">C7</font></th></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">=IF(B7="OWNER",A7,C6)</font></td></tr><tr><th style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">C8</font></th></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">=IF(B8="OWNER",A8,C7)</font></td></tr><tr><th style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">C9</font></th></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">=IF(B9="OWNER",A9,C8)</font></td></tr><tr><th style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">C10</font></th></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">=IF(B10="OWNER",A10,C9)</font></td></tr><tr><th style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">C11</font></th></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">=IF(B11="OWNER",A11,C10)</font></td></tr></table>
 
Last edited:
Upvote 0
Hi Irobbo314

It works! Fantastic

I don't yet see why it works, though, so will need to study the formula.

Thanks a million.

Regards

Wednesday
 
Upvote 0
Hi Irobbo314

Ah, I see now!

It is a very neat solution. I just wish I was smart enough to think of it myself!

Thanks again. You have saved me a lot of time.

Regards

Wednesday
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Formula to return employee's name in cells adjacent to his/her family members
Replies
3
Views
182
Fluff
Fluff
C
  • Question Question
Process to Match IDs to Multiple Cells Based off of Another Cell?
Replies
2
Views
543
shinigamilight
S
P
  • Solved
Need Formula for conditional Formatting
2
Replies
10
Views
1K
Joe4
Joe4
J
  • Question Question
Populate Table with filtered data from another table.
Replies
3
Views
3K
DanteAmor
DanteAmor
L
  • Question Question
creating new cells based on cell value in separate sheet
Replies
3
Views
817
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,173
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top