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 thro...
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))LAMERCADOVEGA Ah, all becomes clear now. The problem is in this belief:
<< It appears that the data in worksheet 2 is ordered by that date-time value... >>
I did not spot it in the picture you first posted, but the worksheet 'WTs CLEAN' is in fact not ordered that way. In fact it's not completely ordered by anything. (E.g., compare the date-time values in rows 14 and 15; note how the dates (but not the times) are in mostly reverse chronological order after row 94.
So, before or after applying your formula, you need to sort the rows. I used this sort, and recommend that you do the same:
Then, the XLOOKUP formula with the search_mode = -1 should work as you desire. Alternatively, you can sort those columns Newest to Oldest and Largest to Smallest, respectively, and use a search_mode of 1.