该列中的Hlookup和最后一个值 [英] Hlookup and Last Value In that Column

问题描述

我正在尝试为以下项目找到Excel公式:

I am trying to find Excel Formula for the following:

我有一个包含每周工资成本的电子表格.第1行具有各种付款元素，包括列Gross Pay.

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

我已将Wk_40作为命名范围分配给所有数据.

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

在摘要表上，我正在使用HLOOKUP公式查找Gross Pay，然后对于row index number，我想选择该列的最后一行.

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.

例如，总薪资"在M列中，而行#427总计该列.在第430行中，如果一周跨两个月，我已按比例进行了计算.我想从第430行中选择金额.

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.

我尝试了

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

但这将返回#N/A.

推荐答案

在我看来，您似乎不太了解HLOOKUP的工作原理.尝试使用此:

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

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

这会在指定的范围内查找Gross Pay(假设它覆盖了从第一行到至少第430行，并且列Gross Pay在第一行的某个位置).

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是要从中提取所需信息的行.

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

注意:

• 您也可以使用0代替FALSE，因为它们在excel中的含义相同.

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

我在公式中使用了"Gross Pay"，因为我不确定F42包含什么(在公式中，您根本没有提到它).如果它具有Gross Pay，则它应该与F42一起使用.

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.

如果@simico正确理解，则可以使用INDEX和两个MATCH来获取Gross Pay列中的最后一个值:

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)获取Gross Pay所在的列号.

INDEX(Wk_40,0,MATCH("Gross Pay",INDEX(Wk_40,1,0),0))然后返回整列.

MATCH(9^99,INDEX(Wk_40,0,MATCH("Gross Pay",INDEX(Wk_40,1,0),0)))然后给出包含值的最后一个单元格.

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.

这篇关于该列中的Hlookup和最后一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！