I have an employee schedule table in excel that shows how many hours a specific employee is scheduled to work each day. (Example screenshot)
I need to build a scenario that can find the number of hours an employee is supposed to work by searching their name in Column A and the day of the week in Row 1.
I tried to approach this in a cartesian manner, by trying to obtain a position value for ‘Name’, and then obtain a position value for ‘Day of the Week’. and then use the two responses to give me a cell value to obtain the number of hours from.
For example looking up how many hours Sam is scheduled to work on Wednesday should return:
Row=4
Column=D
Desired output is the value from Cell D4= ‘10’
I can find their names quite easily with the Excel List Worksheet Rows module to return an array of names that I can then filter.
After spending several days on this now, I cant however seem to replicate this look-up to search columns for a day of the week in the same manner. Search Columns function does not appear to exist.
Ive attempted to use the Retrieve Data module, however am having no success doing a 2-dimentional lookup (both for names and days of the week) to return the number of hours scheduled.
There has to be an elegant way to do a table lookup by searching both rows and columns for dynamic values.
Perhaps much simpler than the 3-step cartesian method I’m attempting.
Can somebody please please help me with this?
Edit: It is crucial that excel/google sheets is used for the original data, as the user will only keep the data updated there, and not in a datastore/custom array within make.com
Welcome to the Make community!
I think you just need a simple “Search Rows” module, which would return all the columns for the person.
Then, you can simply reference the column you want.
2 Likes
Hey @samliew, thank you for the quick response.
Unfortunately there does not appear to be a Search Rows module for Excel.
Ive seen other posts reference it a while back, but maybe it has since been removed by Make.com?
The closest functionality I can find is within the “List Worksheet Rows” module, and you can filter a fixed position within the output for the employees name,
However, I cannot figure out a way to search the bundle “Row” and get the corresponding column for day of the week (Wednesday).
Do you have any suggestions on how to dynamically search the bundle outputs, a few levels deep for the corresponding day of the week (Wednesday)? Then use its result to obtain the corresponding number of hour scheduled for that employee on that day?
Apologies, I assumed you were using Google Sheets, which has Search modules.
If MS 365 Excel does not have such a module, I recommend that raise a feature request to Make to add search modules to this third-party service.
You can submit this suggestion to the Idea exchange, under App improvement ideas.
Don’t forget to search for it first, just in case someone already suggested it, so that you don’t end up creating a duplicate.
2 Likes
@samliew
Thank you, looks like a similar suggestion for the Excel Seach Rows function has been already submitted back in 2022, similarly with the column based search.
Unfortunately despite the large number of upvotes, it does not look like its coming anytime soon.
In the meantime, do you have any other thoughts on how a column lookup can be done?
I am able to use the Excel; “Retrieve Data” or “Get a Table” functions in Excel to import the range, however am struggling with then searching through the resulting complex buddle structure since the data is grouped by “rows”, and I am not sure how to search for a text match and return a bundle-row-position numerical output that could represent the column number.
Still struggling with this.
Has anybody been able to find to do a row and column search in Excel?
