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) :-

MEMBER NAME
STATUSOWNER NAME
JOHN DOE
OWNERJOHN DOE
JANE DOE
SPOUSEJOHN DOE
JAMES DOE
CHILDJOHN DOE
JANICE DOE
CHILDJOHN DOE
JACK DOE
OWNERJACK DOE
JENNY DOE
SPOUSEJACK DOE
JEFFREY DOE
OWNERJEFFREY DOE
JOHN DOE
CHILDJEFFREY DOE
JACK DOE
OWNERJACK DOE
JANET DOE
OWNERJANET DOE

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

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 change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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

C
  • Question
Process to Match IDs to Multiple Cells Based off of Another Cell?
Replies
2
Views
417
shinigamilight
S
P
  • Solved
Need Formula for conditional Formatting
2
Replies
10
Views
890
Joe4
Joe4
J
  • Question
Populate Table with filtered data from another table.
Replies
3
Views
3K
DanteAmor
DanteAmor
L
  • Question
creating new cells based on cell value in separate sheet
Replies
3
Views
673
Fluff
Fluff
T
  • Question
Formula to sum based on multiple criteria -For subscription revenues
Replies
8
Views
955
Sam_D_Ben
S

Forum statistics

Threads
1,214,780
Messages
6,121,527
Members
449,037
Latest member
tmmotairi

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