Auto-Populate Columns Based on Cell-Lookup Result?

PacSum

New Member
Joined
Jan 8, 2017
Messages
30
STARTING POINT:
NameBeginningChange #1Change #2Change #3Ending
Bob
Sarah

<tbody>
</tbody>

Data will be manually populated into the "Name" column above.

After populating into the name column, my goal is to have the subsequent columns automatically populated based on whether or not that name matches to male or female.


GENDER TABLE:
NameGender
BobM
SarahF

<tbody>
</tbody>

EQUATION TABLE:
GenderBeginningChange #1Change #2Change #3Ending
MmEquation#1mEquation#2mEquation#3mEquation#4mEquation#5
FfEquation#1fEquation#2fEquation#3fEquation#4fEquation#5

<tbody>
</tbody>

END GOAL:
NameBeginningChange #1Change #2Change #3Ending
BobmEquation#1mEquation#2mEquation#3mEquation#4mEquation#5
SarahfEquation#1fEquation#2fEquation#3fEquation#4fEquation#5

<tbody>
</tbody>

What would be the best way to go about trying to solve this?

Thanks in advance!
 
Last edited:

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
try

Book1
ABCDEF
1NameBeginningChange #1Change #2Change #3Ending
2BobmEquation#1mEquation#2mEquation#3mEquation#4mEquation#5
3SarahfEquation#1fEquation#2fEquation#3fEquation#4fEquation#5
4
5
6
7
8
9
10NameGender
11BobM
12SarahF
13
14
15
16GenderBeginningChange #1Change #2Change #3Ending
17MmEquation#1mEquation#2mEquation#3mEquation#4mEquation#5
18FfEquation#1fEquation#2fEquation#3fEquation#4fEquation#5
Sheet4
Cell Formulas
RangeFormula
B2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:B2),0)
B3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:B3),0)
C2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:C2),0)
C3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:C3),0)
D2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:D2),0)
D3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:D3),0)
E2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:E2),0)
E3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:E3),0)
F2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:F2),0)
F3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:F3),0)
 
Upvote 0
And to add in case this creates another layer of complexity, the mEquation and fEquation will contain a cell reference to the first column. It is not a static equation. It will need to be able to reference column A.
 
Upvote 0
try
ABCDEF
1NameBeginningChange #1Change #2Change #3Ending
2BobmEquation#1mEquation#2mEquation#3mEquation#4mEquation#5
3SarahfEquation#1fEquation#2fEquation#3fEquation#4fEquation#5
4
5
6
7
8
9
10NameGender
11BobM
12SarahF
13
14
15
16GenderBeginningChange #1Change #2Change #3Ending
17MmEquation#1mEquation#2mEquation#3mEquation#4mEquation#5
18FfEquation#1fEquation#2fEquation#3fEquation#4fEquation#5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4

Worksheet Formulas
CellFormula
B2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:B2),0)
C2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:C2),0)
D2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:D2),0)
E2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:E2),0)
F2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:F2),0)
B3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:B3),0)
C3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:C3),0)
D3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:D3),0)
E3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:E3),0)
F3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:F3),0)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Makes sense. I had thought about this, but then as I was working through realized there was the qualifier that the equations are dynamic and will need to reference the first column. How would I implement a dynamic cell reference?
 
Upvote 0
could you give some examples

I'm sorry, just saw this now!

For example purposes, it'd be similar to a vlookup on the name, but using that specific equation which was previously determined.

For instance, Bob is male. Bob is cell A2.

His "Change #1 " column which is represented by mEquation#2 could be equal to =VLOOKUP(A2,[another table of data],3)+20*12

Sarah is female. Sasrah is cell A3.

Her "Change #1 " column which is determined to be fEquation#2 could be equal to =VLOOKUP(A3,[another table of data],5)+100/2
 
Upvote 0
The formulas posted earlier still work.

Book1
ABCDEF
1NameBeginningChange #1Change #2Change #3Ending
2BobmEquation#1245mEquation#3mEquation#4mEquation#5
3SarahfEquation#157fEquation#3fEquation#4fEquation#5
4
5
6
7
8
9
10NameGender
11BobM
12SarahF
13
14
15
16GenderBeginningChange #1Change #2Change #3Ending
17MmEquation#1245mEquation#3mEquation#4mEquation#5
18FfEquation#157fEquation#3fEquation#4fEquation#5
19
20
21
22bobfsa5
23rickgsh2
24sarahsome7777
Sheet1
Cell Formulas
RangeFormula
B2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:B2),0)
B3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:B3),0)
C2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:C2),0)
C3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:C3),0)
C17=VLOOKUP(A2,A22:C28,3)+20*12
C18=VLOOKUP(A3,A22:E29,5)+100/2
D2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:D2),0)
D3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:D3),0)
E2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:E2),0)
E3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:E3),0)
F2=VLOOKUP(VLOOKUP($A2,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A2:F2),0)
F3=VLOOKUP(VLOOKUP($A3,$A$11:$B$12,2,0),$A$17:$F$18,COLUMNS($A3:F3),0)
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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