Index / Match Help

[jharvey87]

Hi, was hoping someone could help me correct the following formula.

Please Login or Register  to view this content.

I want it to retrieve the number in Column E from the Home BU tab, that has the following criteria:

Employee # matches whats listed in A7. This is matching to Column A in the Home BU tab.

In the Home BU tab, column B must be blank.

I'm just receiving a 0, so not sure whats wrong. I'm assuming its something to do with me trying to reference a cell with nothing in it?

[63falcondude]

Try this:

=INDEX('Home BU'!$E$4:$E$16500,MATCH($A7&"",'Home BU'!$A$4:$A$16500&'Home BU'!$B$4:$B$16500,0)) Ctrl Shift Enter

[José Augusto]

Hi

Please, can you explain what is 'Home BU'!$D$4:$D$16500, in MATCH function ?

[63falcondude]

Or if column E is numbers (and there could only be one match) you can use this:

=SUMIFS('Home BU'!$E:$E,'Home BU'!$A:$A,$A7,'Home BU'!$B:$B,"")