Tables dynamic data validation

[lhannah]

Hello,

Here is my table A2:D7

<TABLE style="WIDTH: 203pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=270 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #4f81bd; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64 height=17>Name</TD><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #4f81bd; BORDER-LEFT: #f0f0f0; WIDTH: 59pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=78>Availability</TD><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #4f81bd; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>Rank</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #4f81bd; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>Age</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=17>DH</TD><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">No</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>1</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>46</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=17>LH</TD><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">No</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>2</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>27</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=17>SK</TD><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">Yes</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>3</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>32</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=17>TD</TD><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">Yes</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>2</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>35</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=17>KJ</TD><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">Yes</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>1</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #f0f0f0; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #f0f0f0; COLOR: black; BORDER-BOTTOM: #f0f0f0; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>30</TD></TR></TBODY></TABLE>

I have a data validation list dropdown in H18 that populates the Name column. As more names are added to the table, the dropdown includes them.

I am trying to write a formula in I18 that returns Age depending on the dropdown... DH returns 46, LH returns 27, etc.

Here is the simple formula, without taking into account the KJ row. How do I rewrite the formula to include more names as my table expands?

=IF(H18=A3,D3,IF(H18=A4,D4,IF(H18=A5,D5,IF(H18=A6,D6))))

Thank you!

 

[Steve=True]

I think it would be better to do a dynamic look up vs a static IF statement. Check out this link:
http://blog.contextures.com/archives/2011/06/20/find-best-price-with-excel-index-and-match/

Here is your specific formula:

=INDEX(A3:D7,MATCH(H18,A3:A7,0),4)

Also, i would change A3:a7 to a named range Names" so you can change the named range vs every formula that looks at it

 

[lhannah]

Thank you! I did make some name adjustments to make it dynamic, and here is what I ended up with:

=INDEX(Names_Ages,MATCH(H18,Names_Ages[Name],0),4)