• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple lookup, both Column and Row simultaneously

aksalim

New Member
I have a worksheet which consists employees attendance for a week. The attendance will be available from a web based App and employees can be log in at site, which will tagged with their geofence (location). The output of the App will be available in xls format. Refer for the sheet 2 (WeekilyAttendance) of my xls file attached.

Now, i need to carryout a multiple lookup for both date and employee code, and fill the Work_Location in the sheet 1 (DailyReport) against each date/for each employee. Is there any shirt cut formula, without modifying the format??
 

Attachments

  • Jun_26 to Jul_ 01_ 2022_Blue Collar Weekily Attendace_Test.xlsx
    117.7 KB · Views: 9
In cell D4 of the DailyReport sheet:
Code:
=TEXTJOIN(", ",TRUE,FILTER(WeekilyAttendnace!$J$2:$J$687,(DailyReport!$A4=WeekilyAttendnace!$B$2:$B$687)*(DailyReport!D$3=WeekilyAttendnace!$A$2:$A$687),"Not found"))
copied across and down.
If there are more than one location for a given date/employee combination, all will be shown in the cell, separated by a comma.
See attached.
 

Attachments

  • Chandoo48313Jun_26 to Jul_ 01_ 2022_Blue Collar Weekily Attendace_Test.xlsx
    132 KB · Views: 6
In cell D4 of the DailyReport sheet:
Code:
=TEXTJOIN(", ",TRUE,FILTER(WeekilyAttendnace!$J$2:$J$687,(DailyReport!$A4=WeekilyAttendnace!$B$2:$B$687)*(DailyReport!D$3=WeekilyAttendnace!$A$2:$A$687),"Not found"))
copied across and down.
If there are more than one location for a given date/employee combination, all will be shown in the cell, separated by a comma.
See attached.

Thanks for the suggestion. However, can we do it without adding a cell/column? in fact "DailyReport" is prepared in compliance with the ERP system and adding any cell will have an impact on it.
 
I don't think I've added any cells, but I'll double-check…

…just done that and no, no extra cells/columns added, only formulae into existing cells.

I suspect you're seeing columns to the right because I changed from Page Break Preview to Normal view in the Workbook Views section of the View part of the ribbon. Change it back.
 
Last edited:
I don't think I've added any cells, but I'll double-check…

…just done that and no, no extra cells/columns added, only formulae into existing cells.

I suspect you're seeing columns to the right because I changed from Page Break Preview to Normal view in the Workbook Views section of the View part of the ribbon. Change it back.
thanks
 
Back
Top