• 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

p45cal

Well-Known Member
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

aksalim

New Member
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.
 

p45cal

Well-Known Member
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:

aksalim

New Member
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
 
Top