HLOOHUP assistance needed

[Antrosenthal]

I am trying to run a HLOOKUP function however can not get it to work.

I am trying to identify a specific date which returns the smallest number in a range of numbers. I would like the HLOOKUP to identify a specific date where a business makes the biggest loss. So if I new that the biggest losses that the company made were say 100,000 I would like to know which month this event occurred in.

1. In cell C30 I have run a MIN calculation to determine what the smallest number is over a range of numbers =MIN(Data!I32:DX32) (Data is the page that I am picking the numbers off)

2. I would then like to determine the date that the MIN number is in, so what I did was in the row above the string of numbers, I ran the dates which were monthly dates increasing by one month at a time. So the HLOOKUP I ran was
=HLOOKUP(C30,Data!I31:DX32,1,FALSE), where C30 represents the number from 1, row 31 are the dates and row 32 are the numbers.

The result I keep getting is #N/A ................very frustrating

 

[dave3009]

Hi and Welcome

Try

=INDEX(Data!I31:DX31, , MATCH(MIN(Data!I32:DX32), Data!I32:DX32, 0))

 

[Antrosenthal]

That worked perfectly. Thanks so much

Is there any way for me to add up a row of data which then stopped adding when the specific date is reached as you described.

So for example if row 1 represented the dates, row 2 represented the monthly profits (losses ) and row 3 represented the accumulative profits (losses). I would like to then add up row 3 however would like the calculation to stop once the date was reached?

 

[dave3009]

Maybe like this

=SUM(OFFSET(Data!I33,,,1,MATCH(MIN(Data!I32:DX32),Data!I32:DX32,0)))