Forum Discussion
I have been looking for Formula for a problem I have
Hi All,
I have been having an issue trying to get a formula to work. I could really use some help! I have sample data that can be looked for reference.
I am looking have the formula look through a table and return the last location based on the last time and date for a list of times.
LAMERCADOVEGA It seems that you are trying to look up the Destination Bin (worksheet 2, column B) with the latest date-time value (worksheet 2, columns F and G) for a specific Handling Unit (worksheet 1, column B and worksheet 2, column A). It appears that the data in worksheet 2 is ordered by that date-time value, which is also in order by Warehouse Task (column D). Do I have that correct?
Yes, most Excel lookup functions are going to give you the first matching occurrence only. And while the XLOOKUP function does that also, it will do a search from the bottom of a range up (specify search_mode = -1).
I don't know the names of your worksheets, but a formula in Sheet1 might then be:
=XLOOKUP( B3, 'Sheet2'!A1:A9999, 'Sheet2'!B1:B9999, "not found", 0, -1 )The apostrophes are required only if your worksheet name contains a space or special character. The spaces are also optional.
(I'm using an end-of-range row of 9999; you may need to use a larger number. Or you might choose to have Excel derive the row number of the last non-empty row and use a "dynamic range" or other construct to refer to the data ranges.)SnowMan55 Yes you are correct.
I was attempting to use the Xlookup function with conditions for the time and date columns and kept getting an error. The formula you provided did return what I was looking for but, when I applied it into the workbook the value was not always the most recent location.
For context, the purpose I am attempting this is so that I can follow the Handling units after the leave the "inventorycage" location . I have been having an issue where the items leaves the location and is not being transacted to a new location. effectively losing it.
There is one more argument that I have to take into consideration which is the whse process category. the process which would determine the "current" location of the handling unit would be an internal warehouse movement.
I will provide a link to the example worksheet if you would like to take a look at it. I am very thankful for your help.
If you have 365 and prefer to not sort your table data, this might work for you:
=LET(order,BYROW(Table3[[Confirmation Date]:[Confirmation Time]],LAMBDA(row,SUM(row))),data,SORTBY(Table3[[Dest. Handling Unit]:[Destination Bin]],Table3[Dest. Handling Unit],1,order,-1),VLOOKUP([@[Handling Unit]],data,2,0))
