Forum Discussion
xlookup in a table, how to find a cell when knowing how to find the column and how to find the row
In a table I have information of inventory of all items on all dates in a month. By using XLOOKUP I can easy find the item and also by using XLOOKUP I can easy find the date, But how do I use both in the same formula, so I can find the cell with the information for the right item on the right date?
2 Replies
SEN8660 Since your Excel version supports XLOOKUP, you can also use the FILTER function. Use that one in stead. The formula structure will than then be like this:
=FILTER( data_range, (item_column=item) * (date_column=date) )
where "data_range" points to the one or more columns from which you want to extract the data for an item on a particular date.
Do you want to return the cell numbers in which the item and the corresponding date are found? Select the item in the dropdown in cell F2 in the attached file and check the results in cells G2, H2 and I2. Maybe this is what you want to do.
Or do you want to return a value that corresponds to the item and its date as shown in cell G5 in the attached file. Then you can apply below formula.
=VLOOKUP(F2&G2,CHOOSE({1,2},B2:B27&C2:C27,D2:D27),2,FALSE)
This can be done with XLOOKUP accordingly but i work with Excel 2013 that's why i use VLOOKUP.
