Excel 365 XLOOKUP - New User Question

Question

Hi,I am trying to use the XLOOKUP function to retrieve duplicate values from 1 piece of criteria. The formula below is where i am at.&nbsp;=XLOOKUP(F2:F1500,Q2:Q309,Q2:R309,,-1)&nbsp;I am looking up a code number as criteria "03-01" (1 of many in a column)I have multiple duplicate matches that i want returned from another column but it only returns the first match. As i drag it down the column it keeps returning the first match.&nbsp;How do i get it to return all matches as i drag it down the column.&nbsp;Thanks&nbsp;Terry

mathetes · Answer

Terry_M09&nbsp;&nbsp;You might be better served by the FILTER function. It will return multiple rows that match a given criterion, or several criteria....&nbsp;&nbsp; Here are two sources that might help you accomplish your task with that function.https://exceljet.net/excel-functions/excel-filter-function&nbsp;https://www.youtube.com/watch?v=9I9DtFOVPIg&nbsp;

terry_m09 · Answer

Thanks. I am using the filter function now but i have to cut and paste down my sheet changing the criteria cell reference as i go. I need something a bit easier.mathetes&nbsp;

mathetes · Answer

Terry_M09&nbsp;&nbsp;Is it possible to post a copy of your spreadsheet then? I think we need a bit more of an understanding of the full context here. Just make sure there's nothing confidential or private in the sheet.

terry_m09 · Answer

Hi,I have attached part of the file with no personal identifiable info included.&nbsp;Column F holds a list of codes some duplicate relating to the amount of duplicate entries in the sheet that code has.&nbsp;Column G hold asset codes duplicated the number of times the code in F is duplicated.&nbsp;Column H holds the returned filtered duplicate values which i want to return using XLOOKUP.&nbsp;The return array uses column Q and R which the duplicate values to be returned.&nbsp;I hope that makes sense.&nbsp;I would like to put the XLOOKUP in column H and drag it down the column to returm my duplicate matches for the code in column F&nbsp;Thanks.mathetes&nbsp;

mathetes · Answer

Terry_M09&nbsp;&nbsp;You wrote: I hope that makes sense.&nbsp;I'm afraid it doesn't. I don't really get the ultimate task here. Nor the logic (or intended logic) behind the codes in the various columns. I can see why XLOOKUP and FILTER aren't working...I don't think either is intended to work within the table that they're a part of. They're intended as tools to sit outside of a table/database and extract a row [XLOOKUP] or set of rows [FILTER] that meet criteria.&nbsp;But I suspect that there's a deeper issue regarding the basic database itself.

Forum Discussion

Excel 365 XLOOKUP - New User Question

6 Replies

Resources