0

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.

Jerry
  • 70,495
  • 13
  • 100
  • 144
Malik
  • 17
  • 2
  • 6
  • If the answer helped you, could you please click on the checkmark outline? That's the conventional way to thank people here, instead of editing their answer :) – Jerry Feb 17 '14 at 05:16
  • Hi Jerry, although I have clicked on the checkmark, but the answer of my question was really in the comments. "=HLOOKUP("Gross Pay", WK_40,430,FALSE) which was in the answer, was not the answer I was looking for. The formula which resolved my issue was "=HLOOKUP("Gross Pay",Wk_40,MATCH(9^99,INDEX(Wk_40,0,MATCH("Gross Pay",INDEX(Wk_40,1,0),0))),0)", hence I replied in the comments. Thanks anyway, your help is much appreciated. – Malik Feb 22 '14 at 22:17

2 Answers2

1

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.

Jerry
  • 70,495
  • 13
  • 100
  • 144
  • 1
    Jerry, as I understood the Q, OP `want to select the last row of that column` and it's not always 430. That's why Op's used `MATCH(9.99999999999999E+307)`. Basically correct one should be `MATCH(9.99999999999999E+307, C:C)` where C:C is the column, with `Gross Pay`, but the problem is to determin column address – Dmitry Pavliv Feb 16 '14 at 16:41
  • Jerry, `MATCH("Gross Pay",1:1,0)` gives you column number starting from first column, e.g. if `"Gross Pay"` in column `I`, it would give you `9`. Now, if `Wk_40` say `G1:I7`, `INDEX(Wk_40,0,MATCH("Gross Pay",1:1,0))` would evaluates to `INDEX(G1:I7,0,9)` and gives you #REF, since `G1:I7` hasn't 9th column.. – Dmitry Pavliv Feb 16 '14 at 17:05
  • Yup, it's true, in my example `G1:I7` contains `Gross Pay` (column `I`), because I sad `if "Gross Pay" in column I`..but the problem is that you counts column of `Gross Pay` starting from column `A`, but your `Wk_40` not necessarily starts from column A – Dmitry Pavliv Feb 16 '14 at 17:11
  • see [example image](https://www.dropbox.com/s/3p6d6e9p2eot7ky/test.png). My `Wk_40` defined as `G1:I7`, and `MATCH("Gross Pay",1:1,0)` returns 9, but `Wk_40` has only 3 columns – Dmitry Pavliv Feb 16 '14 at 17:18
  • @simoco is 100% right and you dont even know if Wk_40 start in the first worksheet row – CRondao Feb 16 '14 at 17:23
  • @Jarry, as I see you should make last small edit: change `INDEX(Wk_40,1)` to `INDEX(Wk_40,1,0)`:) – Dmitry Pavliv Feb 16 '14 at 17:39
  • it gives me `#REF` but with `INDEX(Wk_40,1,0)` gives correct result:) – Dmitry Pavliv Feb 16 '14 at 17:41
  • and change it also in the formula itself:) – Dmitry Pavliv Feb 16 '14 at 17:44
  • It is only optional if range contains only one column – CRondao Feb 16 '14 at 17:48
1

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.

CRondao
  • 1,883
  • 2
  • 12
  • 10