# max value in column and related time

#### Mollymm

##### New Member
I have 2 columns of numbers, A is time and B is a list of numbers.
I need to find the find and record the number in column B that is the greatest diffference from 0, it may be positive or negative. Then report the corresponding time from column A.

Thank you for helping.

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### steve the fish

##### Well-known Member
Try this. As with vlookup it will return the first value in event there are two the same. Has to be entered CNTL-SHIFT-ENTER. Works for 100 cells each column. If you need any more adjust.

=INDEX(A1:A100,MATCH(MAX(ABS(B1:B100)),ABS(B1:B100),0))

#### Mollymm

##### New Member
Thank you for the help.
When I enter this I am getting the time (from column A) which I need, but not the corresponding (max) number from column B

#### steve the fish

##### Well-known Member
This will do that:

=MAX(ABS(B1:B100))

#### Mollymm

##### New Member
Hopefully one last question, thank you for your patience.

=MAX(ABS(B1:B100)) gives me the max abs, yes. If the number is negative I loose that information, is there a way to regain it?

#### steve the fish

##### Well-known Member
Try this:

=IF((MAX(B1:B100)+MIN(B1:B100))>0,MAX(B1:B100),MIN(B1:B100))

#### Mollymm

##### New Member
Thank you so much!

Replies
2
Views
397
Replies
12
Views
369
Replies
12
Views
692
Replies
23
Views
2K
Replies
3
Views
372

1,195,594
Messages
6,010,625
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back