Need help finding the account with the lowest and highest value from several sheets.

[USAMax]

I am trying to return the account number with the lowest and highest values. I tried to use this formula to return the lowest value.

=MIN(AT3,BJ3,BZ3,CP3,DF3,DV3,EL3,FB3,FR3,GH3,GX3,HN3,ID3,IT3,JJ3)
=MAX(AT3,BJ3,BZ3,CP3,DF3,DV3,EL3,FB3,FR3,GH3,GX3,HN3,ID3,IT3,JJ3)

Account # Column
206-1 AT
206-2 BJ
206-3 BZ
206-4 CP
206-5 DF
206-6 DV
206-7 EL
208-1 FB
208-2 FR
208-3 GH
208-4 GX
208-5 HN
208-6 ID
208-7 IT
208-8 JJ

I need to know which account number has the lowest and highest value excluding Zero Values. Any suggestions or direction would be greatly appreciated.

The value I am looking for is 14 columns to the left in row 1.

Return Value
Account # Cell
206-1 AF1
206-2 AV1
206-3 BL1
206-4 CB1
206-5 CR1
206-6 DH1
206-7 DX1
208-1 EN1
208-2 FD1
208-3 FT1
208-4 GI1
208-5 GZ1
208-6 HP1
208-7 IF1
208-8 IV1

Dave

 

[SanjayGMusafir]

I can understand somewhat what you want but can't work unless I get to see the data sample. Upload same using XL2BB to understand how to populate formula(e) as per your requirement.

 

[USAMax]

I can understand somewhat what you want but can't work unless I get to see the data sample. Upload same using XL2BB to understand how to populate formula(e) as per your requirement.

Hi Sanjay, I do not know where to work with XL2BB. I downloaded the file but the message I get is, "This file type is not supported in Protected View." My Min and Max return the correct values, I just need the offset of the correct value to be returned. The offset is 14 columns left, in the first row.

 

[SanjayGMusafir]

I am trying to return the account number with the lowest and highest values. I tried to use this formula to return the lowest value.

=MIN(AT3,BJ3,BZ3,CP3,DF3,DV3,EL3,FB3,FR3,GH3,GX3,HN3,ID3,IT3,JJ3)
=MAX(AT3,BJ3,BZ3,CP3,DF3,DV3,EL3,FB3,FR3,GH3,GX3,HN3,ID3,IT3,JJ3)

Account # Column
206-1 AT
206-2 BJ
206-3 BZ
206-4 CP
206-5 DF
206-6 DV
206-7 EL
208-1 FB
208-2 FR
208-3 GH
208-4 GX
208-5 HN
208-6 ID
208-7 IT
208-8 JJ

I need to know which account number has the lowest and highest value excluding Zero Values. Any suggestions or direction would be greatly appreciated.

The value I am looking for is 14 columns to the left in row 1.

Return Value
Account # Cell
206-1 AF1
206-2 AV1
206-3 BL1
206-4 CB1
206-5 CR1
206-6 DH1
206-7 DX1
208-1 EN1
208-2 FD1
208-3 FT1
208-4 GI1
208-5 GZ1
208-6 HP1
208-7 IF1
208-8 IV1

Dave

Try this -

I'm giving you on the basis of raw information you have provided -

For Lowest Value

=ADDRESS(1,MATCH(MIN(AT3,BJ3,BZ3,CP3,DF3,DV3,EL3,FB3,FR3,GH3,GX3,HN3,ID3,IT3,JJ3),A3:JJ3,0)-14,4)

For Highest Value

=ADDRESS(1,MATCH(MAX(AT3,BJ3,BZ3,CP3,DF3,DV3,EL3,FB3,FR3,GH3,GX3,HN3,ID3,IT3,JJ3),A3:JJ3,0)-14,4)

In Case you want Hyperlink to jump directly to that cell then try this -

For Lowest Value

=HYPERLINK("#"&ADDRESS(1,MATCH(MAX(AT3,BJ3,BZ3,CP3,DF3,DV3,EL3,FB3,FR3,GH3,GX3,HN3,ID3,IT3,JJ3),A3:JJ3,0)-14,4))

For Highest Value

=HYPERLINK("#"&ADDRESS(1,MATCH(MAX(AT3,BJ3,BZ3,CP3,DF3,DV3,EL3,FB3,FR3,GH3,GX3,HN3,ID3,IT3,JJ3),A3:JJ3,0)-14,4))

 

[USAMax]

Thank you so much Sanjay. I am so very sorry about this late reply, but my wife had a surgery and I have to take care of her.

Your code returns the cell that I am looking for however I am looking for the value within the cell. This is my solution that does not work:

=range(""" & ADDRESS(1,MATCH(MIN(AT3,BJ3,BZ3,CP3,DF3,DV3,EL3,FB3,FR3,GH3,GX3,HN3,ID3,IT3,JJ3),A3:JJ3,0)-14,4) & """)

By the way, thank you for the function Address. This is the first time I have seen it and I've been doing this since 1996.