Look Up 2 Values???

[kyle2004uk]

I am trying to do a look up so that i can look up the name in column A and then the week in column B and return the figure that correspondes to both.

e.g. look up "Jodie", "week 4" and return the figure 6
i cant remove the column A as i need to have multiple names

e.g. jodie and figures week 1 - 10
tim and figures week 1 - 10
etc etc.

 

[Ron Coderre]

Using your posted example
and
G1: a name to find.....Jodie
H1: a week to find.....4

This regular formula returns the Col_D value that corresponds to that combination
I1: =INDEX(D1:D20,MATCH(G1&"_"&H1,INDEX(A1:A20&"_"&B1:B20,0),0))

or...if you have Excel 2007 or later...
I1: =SUMIFS(D1:D20,A1:A20,G1,B1:B20,H1)

Is that something you can work with?

 

[markmzz]

Maybe this (two formulas H2 or I2 - choose one):

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Name</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Week</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Target</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Actual</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Name</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Week</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Figure</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Figure</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">Jodie</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">Jodie</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">6</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">Jodie</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center">Jodie</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">Jodie</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center">Jodie</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center">Jodie</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center">Jodie</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: center">Jodie</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: center">Jodie</TD><TD style="TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: center">Jodie</TD><TD style="TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD><TD style="TEXT-ALIGN: center">******</TD></TR></TBODY></TABLE>Sheet2


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>I2</TH><TD style="TEXT-ALIGN: left">=INDEX(D2:D11,SUMPRODUCT(--(F2&G2=A2:A11&B2:B11),(ROW(A2:A11)-ROW(A2)+1)))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>H2</TH><TD style="TEXT-ALIGN: left">{=INDEX(D2:D11,MATCH(F2&G2,A2:A11&B2:B11,0))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

</TD></TR></TBODY></TABLE>

Markmzz

 

[T. Valko]

I am trying to do a look up so that i can look up the name in column A and then the week in column B and return the figure that correspondes to both.

e.g. look up "Jodie", "week 4" and return the figure 6
i cant remove the column A as i need to have multiple names

e.g. jodie and figures week 1 - 10
tim and figures week 1 - 10
etc etc.

Try this array formula**:

=INDEX(D2:D11,MATCH(4,IF(A2:A11="Jodie",B2:B11),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.