Display a clumn value based on the adjacent cell's value...

[GS7CLW]

I have a ws with values (column B) determined by manual input.
I can determine the highest number (value) but, I need to display
the corresponding NAME (in column A) on another sheet:

<TABLE style="WIDTH: 193pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=258><COLGROUP><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4320" width=135><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 3936" width=123><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 101pt; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 height=19 width=135>A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 92pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 width=123>B</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=19>Columbus North 2P</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>0</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=19>Columbus West 2R</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>12</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=19>Westerville 2T</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>4</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=19>Columbus East 2W</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>9</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=19>OSU \ OCR 2Z</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>0</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=19>Huntington 3D</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>11</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=19>Charleston 3E</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>3</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=19>Parkersburg 3L</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>4</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=19>Marietta 4C</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>9</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=19>Zanesville 4H</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>11</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=19>Newark 4N</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>9</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=19>Xenia 6A</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>3</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=19>Springfield 6B</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>2</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=19>Middletown 6C</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>6</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=19></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69>=MAX(B1:B15)</TD></TR></TBODY></TABLE>

so, in the example above 12 is the highest value in Column B, (today
that is, tomorrow it may be a different organization) so, how do I
display Columbus West 2R in a cell on another sheet (this sheet is YTD):

so on another sheet; the formula would be:
=YTD! _______________ 'plus whatever I need to determine the
contents of the adjacent cell (in THIS case: Columbus West 2R ).

I hope I explained myself well enough!!

ONLY 2 MORE DAYS TIL =MAX(FRIDAY)!!!!!

cliff

 

[gsistek]

=lookup(max('ytd'!b1:b15),'ytd'!b1:b15,'ytd'!a1:a15)

 

[VoG]

Try

=INDEX(YTD!A:A,MATCH(MAX(YTD!B:B),YTD!B:B,0))

 

[njimack]

=index(ytd!$a$1:$a$14,match(max(ytd!$b$1:$b$14),ytd!$b$1:$b$14,0))

 

[GS7CLW]

Re: Display a column value based on the adjacent cell's value...

i have tried all three methods; replacing the cell values in your formuli with the actual cells involved:
A4 TO A35 ORG names
R4 TO R35 highest values

Below are all 3 methods and the actual cells used below:

=LOOKUP(MAX('YTD'!B1:B15),'YTD'!B1:B15,'YTD'!A1:A15)
=LOOKUP(MAX('YTD'!R4:R35),'YTD'!R4:R35,'YTD'!A4:A35)
 
=INDEX(YTD!A:A,MATCH(MAX(YTD!B:B),YTD!B:B,0)) 
=INDEX(YTD!A:A,MATCH(MAX(YTD!R:R),YTD!R:R,0)) 
 
=INDEX(YTD!$A$1:$A$14,MATCH(MAX(YTD!$B$1:$B$14),YTD!$B$1:$B$14,0))
=INDEX(YTD!$A$4:$A$35,MATCH(MAX(YTD!$R$4:$R$35),YTD!$R$4:$R$35,0))

all 3 methods display a "FALSE" in the cell?????????????............

 

[VoG]

This is what I got

Excel Workbook
	A	B	C	D	E	F	G
1	Columbus North 2P	0					Columbus West 2R
2	Columbus West 2R	12
3	Westerville 2T	4
4	Columbus East 2W	9
5	OSU \ OCR 2Z	0
6	Huntington 3D	11
7	Charleston 3E	3
8	Parkersburg 3L	4
9	Marietta 4C	9
10	Zanesville 4H	11
11	Newark 4N	9
12	Xenia 6A	3
13	Springfield 6B	2
14	Middletown 6C	6
YTD

 

[GS7CLW]

ok, i had a total row in column r so i had to go with:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
=INDEX(YTD!A3:A35,MATCH(MAX(YTD!B3:B35),YTD!B3:B35,0))

which works perfectly if i put this formula on the same sheet.<o></o>
<o> </o>
but I am trying to put this value (the org name) on another sheet (CvrSheet), when I put this formula on another sheet I get the FALSE...

I even tried putting this formula on the same page (YTD) then reference the cell on another sheet (ie
in Cell A45 I put:


=INDEX(YTD!A3:A35,MATCH(MAX(YTD!B3:B35),YTD!B3:B35,0))

then on the CvrSheet I put =YTD!A45, in a cell but I still get the FALSE in the cell.

 

[VoG]

I don't know why you would get that.

Excel Workbook
	A
1	Columbus West 2R
CvrSheet

 

[GS7CLW]

some way some how this cell must have been formatted for boolean or something -- I pasted the cell contents from another cell (including
formatting and everything into that cell then I put the formaula back in an NOW it's perfect.

THANK YOU!!!!
THANK YOU!!!!
THANK YOU!!!!
THANK YOU!!!!
THANK YOU!!!!
THANK YOU!!!!
THANK YOU!!!!
THANK YOU!!!!
THANK YOU!!!!
THANK YOU!!!!
THANK YOU!!!!
THANK YOU!!!!
THANK YOU!!!!
THANK YOU!!!!
THANK YOU!!!!
THANK YOU!!!!

Have a great weekend!

cliff