Hlookup / vlookup with multiple criteria

[nkunni]

Hi Friends

I am working in a company there more than 150 staff working. I make a daily attendance register.

But I confused in separate to only in “Leave” in the specified date, for make as a Leave report.

I just try with Hlookup and failed.

Any idea to make the report in simple method ?

Sample file attached.

 

[azumi]

Hope this help, im using array formula

regards

 

[bosco_yip]

1] Try to select data in the dropdown list of E27 and E28

2] In D30, non-array formula across to F30 and all copy down :

=IFERROR(INDEX(D$13:D$19,AGGREGATE(15,6,(ROW($G$13:$G$19)-ROW($G$13)+1)/($G$13:$Q$19=$E$27)/($G$12:$Q$12=$E$28),ROWS($1:1))),"")

3] In G30, formula copy down :

=IF(D30="","",E$27)

4] Please refer to the attached file.

Regards
Bosco

 

[nkunni]

Dear Bosco_yip,

If for further .... for example 05/12/2016 add some entry, have no any figures / data appear, why?

 

[bosco_yip]

Hi,

It is worked for me when I added "P" or "L" in column of dated 5 Dec 2016

See attached file

Regards
Bosco

 

[nkunni]

Hope this help, im using array formula

regards

Dear azumi.......... the coll one, but I have a doubt that -you write in the formula "L" . If we put the cell reference ie $E$27 - because some time the Half day (HF) would be in the sheet.

First I make the Leave reference, and after the same way to HF in the same range and criteria.

 

[azumi]

Okay nkunni I'm fix' the formula, hope you see the attachment file

 

[nkunni]

Thanks ......... Thanks a lot