Use HLookup to find last row of data in a column

[markrennolds]

Hi All

I need a hlookup formula that will find the last row value but i dont know which row that will be as the data table array will grow daily as more sales are added to the data.
The HLookup will find the sales advisors name but I then need it to find the last vertical row within that column.

Hope that makes sense.
Thanks
Mark

[benishiryo]

hi Mark. as you might already know, a sample excel file explains much more. say your data is as such.
Data Range

	A	B	C	D	E
1		Adam	Benne	Charlie	Dylan
2	17-Jan-14	845	71	311	870
3	18-Jan-14	313	547	320	580
4	19-Jan-14	822	395	699	540
5	20-Jan-14	41	781	199	530

a HLOOKUP for Benne for the last data would be:
=HLOOKUP("Benne",A1:E5,5,0)

if your concern is how to get 5, then:
=HLOOKUP("Benne",A1:E5,COUNTA(A:A)+1,0)

COUNTA counts how many non-blank cells are filled in Column A. i added 1 for the blank cell in A1 i must also count.

if i want to base on whichever name column, then:
=HLOOKUP("Benne",A1:E5,COUNTA(INDEX(B:E,,MATCH("Benne",B1:E1,0))),0)

[Richard Buttrey]

Hi,

The usual way to find the last row in a column of numeric values is

Please Login or Register  to view this content.

of course you can be a little more realistic with the lookup value. If you know for certain that the column will never exceed a particular value then use that value instead of the largest possible 9.99.....E+307

[martindwilson]

another is using benishiryo's table where g1 contains the name of interest
=LOOKUP(10^99,INDEX($A$1:$E$20,,MATCH($G$1,$A$1:$E$1,0)))

[markrennolds]

Hi All

Thanks for the replies. With more study then this is more complex than i initially thought.
I have attached the excel sheet in the format I will be using it.
The data I need to find is the "Sales Order Value SAPCO" which in this example starts in column F for the 1st employee & finishes in column Z. This however may not be the case in each store in the company as we all have different size teams. So the formula needs to recognise to start HLookup @ "Sales Order Value SAPCO" & finish @ ""GIS Value of Order".

The value I need it to find is the last value in the column under each employee, again this will change daily as more sales are added throughout the period.

The lookup value will be the employee number in row 14 & i don't expect this will change but is there a way around this if it did?

Many thanks
Mark

[markrennolds]

Using Martin's formula above, it seems to have worked & only finds first employee number & then uses that data so seems all good.
Thank you.

=IF(ISNA(LOOKUP(10^99,INDEX('Data Drop'!$F$14:$AZ$10000,,MATCH($B10,'Data Drop'!$F$14:$AZ$14,0)))),"",LOOKUP(10^99,INDEX('Data Drop'!$F$14:$AZ$10000,,MATCH($B10,'Data Drop'!$F$14:$AZ$14,0))))