Lookup by date

[sarah04]

Excel idiot here I need help with a lookup that I'm sure it super easy... If I wanted to look up the current week's values for each of these Employees, what formula would I use?

Excel Workbook
	C	D
47	by Week
48	Measure	9/6/2009
49	Observations
50	Andy	4
51	Beth	3
52	Caleb	2
53	Improvements
54	Andy	4
55	Beth	3
56	Caleb	2
Analytics

Thanks for your help

 

[Robert Mika]

Are your weeks along row 48?
Is your formula in D54 and the lookup table C50:D52?

 

[sarah04]

Maybe this will help, the top table is the data, the second table shows what I need in a summary table. The dates do continue along the top row (here, row 63). I need to have a total of 6 lookups I believe, one each for each employee's Observations and Improvements.

Analytics

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 79px"><COL style="WIDTH: 79px"><COL style="WIDTH: 79px"><COL style="WIDTH: 79px"><COL style="WIDTH: 79px"><COL style="WIDTH: 79px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>BZ</TD><TD>CA</TD><TD>CB</TD><TD>CC</TD><TD>CD</TD><TD>CE</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">62</TD><TD>Existing Data</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">63</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">5/1/2011</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">5/8/2011</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">5/15/2011</TD><TD style="TEXT-ALIGN: right; FONT-WEIGHT: bold">5/22/2011</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000; FONT-WEIGHT: bold">5/29/2011</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">64</TD><TD style="FONT-WEIGHT: bold">Observations</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="COLOR: #ff0000"> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">65</TD><TD style="FONT-WEIGHT: bold">Andy</TD><TD style="TEXT-ALIGN: right">201</TD><TD style="TEXT-ALIGN: right">201</TD><TD style="TEXT-ALIGN: right">201</TD><TD style="TEXT-ALIGN: right">201</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">201</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">66</TD><TD style="FONT-WEIGHT: bold">Beth</TD><TD style="TEXT-ALIGN: right">197</TD><TD style="TEXT-ALIGN: right">197</TD><TD style="TEXT-ALIGN: right">197</TD><TD style="TEXT-ALIGN: right">197</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">197</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">67</TD><TD style="FONT-WEIGHT: bold">Caleb</TD><TD style="TEXT-ALIGN: right">191</TD><TD style="TEXT-ALIGN: right">191</TD><TD style="TEXT-ALIGN: right">191</TD><TD style="TEXT-ALIGN: right">191</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">191</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">68</TD><TD style="FONT-WEIGHT: bold">Improvements</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="COLOR: #ff0000"> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">69</TD><TD style="FONT-WEIGHT: bold">Andy</TD><TD style="TEXT-ALIGN: right">55</TD><TD style="TEXT-ALIGN: right">55</TD><TD style="TEXT-ALIGN: right">55</TD><TD style="TEXT-ALIGN: right">55</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">55</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">70</TD><TD style="FONT-WEIGHT: bold">Beth</TD><TD style="TEXT-ALIGN: right">55</TD><TD style="TEXT-ALIGN: right">55</TD><TD style="TEXT-ALIGN: right">55</TD><TD style="TEXT-ALIGN: right">55</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">55</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">71</TD><TD style="FONT-WEIGHT: bold">Caleb</TD><TD style="TEXT-ALIGN: right">52</TD><TD style="TEXT-ALIGN: right">52</TD><TD style="TEXT-ALIGN: right">52</TD><TD style="TEXT-ALIGN: right">52</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">52</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">72</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">73</TD><TD style="FONT-WEIGHT: bold">Desired Summary table</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">74</TD><TD style="FONT-WEIGHT: bold"> </TD><TD style="COLOR: #ff0000; FONT-WEIGHT: bold">(current week)</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">75</TD><TD style="FONT-WEIGHT: bold">Observations</TD><TD style="COLOR: #ff0000"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">76</TD><TD style="FONT-WEIGHT: bold">Andy</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">201</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">77</TD><TD style="FONT-WEIGHT: bold">Beth</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">197</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">78</TD><TD style="FONT-WEIGHT: bold">Caleb</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">191</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">79</TD><TD style="FONT-WEIGHT: bold">Improvements</TD><TD style="COLOR: #ff0000"> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">80</TD><TD style="FONT-WEIGHT: bold">Andy</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">55</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">81</TD><TD style="FONT-WEIGHT: bold">Beth</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">55</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">82</TD><TD style="FONT-WEIGHT: bold">Caleb</TD><TD style="TEXT-ALIGN: right; COLOR: #ff0000">52</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[Robert Mika]

Try this:
BZ74=5/29/2011

For observation:

BZ76
=INDEX($BY$65:$CD$67,MATCH(BY76,$BY$65:$BY$67,0),MATCH(BZ$74,$BZ$63:$CD$63,0))

copy down

For Improvements:
BZ80=INDEX($BY$69:$CD$71,MATCH(BY80,$BY$69:$BY$71,0),MATCH(BZ$74,$BZ$63:$CD$63,0))

copy down

 

[sarah04]

Great, thank you! Is there a way to make the date automatically update? I update this sheet weekly.

 

[Robert Mika]

Try:
For observation:
=IF(ISNUMBER(MATCH($BZ$74,$BZ$63:$CR$63,0)),INDEX($BY$65:$CR$67,MATCH(BY76,$BY$65:$BY$67,0),MATCH(BZ$74,$BZ$63:$CR$63,0)),"")
copy dow
For improvementa:
=IF(ISNUMBER(MATCH($BZ$74,$BZ$63:$CR$63,0)),INDEX($BY$69:$CR$71,MATCH(BY80,$BY$69:$BY$71,0),MATCH(BZ$74,$BZ$63:$CR$63,0)),"")
copy down
Extend the ranges in red as far as you need.