General Lookup Help

[jared511]

Hello,

I was wondering if it is possible to lookup a value in a row, and have it then return the last value in that same column. So if I am doing a lookup for "FrankC", it would search the row that I specify, and when it found "FrankC" it would return the last value under "FrankC" in the same column "FrankC" is in. Bottom line is that the column with "FrankC" has information by month, and I want the sum of all that info which is the last number in the column.



Thanks,
Jared

 

[T. Valko]

Hello,

I was wondering if it is possible to lookup a value in a row, and have it then return the last value in that same column. So if I am doing a lookup for "FrankC", it would search the row that I specify, and when it found "FrankC" it would return the last value under "FrankC" in the same column "FrankC" is in. Bottom line is that the column with "FrankC" has information by month, and I want the sum of all that info which is the last number in the column.

If you have data like this in the range A1:D2

<TABLE style="WIDTH: 152pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=200 border=0 x:str><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" span=4 width=50><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 38pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=50 height=17>Sue</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 38pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=50>Tom</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 38pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=50>Frank</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 38pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=50>Lisa</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>53 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">45 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">90 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">72 </TD></TR></TBODY></TABLE>

Then:

=SUMIF(A1:D1,"Frank",A2:D2)

Or, using a cell to hold the "lookup" value:

A10 = Frank

=SUMIF(A1:D1,A10,A2:D2)

 

[Peter_SSs]

Jared

Are all the totals on the same row? If so, try this. Otherwise try and give a bit more information about what you have in your sheet, and where.

H2 copied down (if needed on multiple rows).

Excel Workbook
	A	B	C	D	E	F	G	H
1	Name	Sue	Tom	Frank	Lisa		Name	Total
2		6	7	6	1		Frank	11
3		6	4	3	3		Sue	17
4		5	5	2	5
5	Total	17	16	11	9
6
Total

 

[T. Valko]

Jared

Are all the totals on the same row? If so, try this. Otherwise try and give a bit more information about what you have in your sheet, and where.

H2 copied down (if needed on multiple rows).

Total

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 10pt">Name</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">Sue</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">Tom</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">Frank</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">Lisa</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt">Name</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">Total</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">6</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">7</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">6</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">1</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt">Frank</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">11</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">6</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">4</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">3</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">3</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt">Sue</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">17</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">5</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">5</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">2</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">5</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 10pt">Total</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">17</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">16</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">11</TD><TD style="FONT-SIZE: 10pt; TEXT-ALIGN: right">9</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD><TD style="FONT-SIZE: 10pt"></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>H2</TD><TD>=INDEX($A$5:$E$5,MATCH(G2,$A$1:$E$1,0))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Or:

=SUMIF(B1:E1,G2,B5:E5)

 

[jared511]

Thanks worked great!