CODEWITHSUNDEEP
excelexcel-formula
Malik
Malik

Reputation: 17

Hlookup and Last Value In that Column

I am trying to find Excel Formula for the following:

I have a spreadsheet with weekly payroll cost. Row 1 is having various payment elements, including a column Gross Pay.

I have assigned Wk_40 as named range to all the data.

On the summary sheet, I am using HLOOKUP formula to look for Gross Pay, and then for row index number, I want to select the last row of that column.

For example, Gross Pay is in column M, and Row # 427 is having a total of that column. In row # 430, I have done a pro-rata calculation, if the week is straddling across two months. I want to select amount from row # 430.

I tried 

=hlookup($F$42,Wk_40,MATCH(9.99999999999999E+307),false)

but this is returning #N/A.

Upvotes: 0

Views: 3036

Answers (2)

Jerry
Jerry

Reputation: 71598

To me, it seems that you don't seem to understand how HLOOKUP works. Try using this:

=HLOOKUP("Gross Pay",Wk_40,430,FALSE)

This looks for Gross Pay in the named range (assuming that it covers from 1st row to at least the 430th row and has the column Gross Pay somewhere in the 1st row).

430 is the row from which the required information is to be extracted.

Note:

  • you can also use 0 instead of FALSE, since they mean the same thing in excel.

  • I used "Gross Pay" in the formula because I'm not sure what F42 contains (you didn't mention it at all in your question until the formula). If it has Gross Pay, then it should be working with F42.

If @simico understood correctly, then you could perhaps use INDEX and two MATCH to get the last value in the Gross Pay column:

=HLOOKUP("Gross Pay",Wk_40,MATCH(9^99,INDEX(Wk_40,0,MATCH("Gross Pay",INDEX(Wk_40,1,0),0))),0)
                           ^--------------------------------------------------------------^

MATCH(0,INDEX(Wk_40,1,0),0) gets the column number where Gross Pay is.

INDEX(Wk_40,0,MATCH("Gross Pay",INDEX(Wk_40,1,0),0)) then returns the whole column.

MATCH(9^99,INDEX(Wk_40,0,MATCH("Gross Pay",INDEX(Wk_40,1,0),0))) then gives the last cell that contains a value.

Upvotes: 1

CRondao
CRondao

Reputation: 1903

WIth this you get the correct row number in the correct column...then with index and a very big formula later you get the result:

=MATCH(9.99999999999999E+307;INDEX(Wk_40;0;MATCH("Gross Pay";INDEX(Wk_40;1;0);0));1)

Like this:

=INDEX(Wk_40;MATCH(9.99999999999999E+307;INDEX(Wk_40;0;MATCH("Gross Pay";INDEX(Wk_40;1;0);0));1);MATCH("Gross Pay";INDEX(Wk_40;1;0)))

Assuming that Wk_40 contains all the data and the first row of Wk_40 contains headers where you can find "Gross Pay". And, of course, besides the header you only have numeric values.

Upvotes: 1

Related Questions