Excel 365 XLOOKUP - New User Question

[Terry_M09]

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.

=XLOOKUP(F2:F1500,Q2:Q309,Q2:R309,,-1)

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.

How do i get it to return all matches as i drag it down the column.

Thanks 

Terry

[#2]

@Terry_M09 

You might be better served by the FILTER function. It will return multiple rows that match a given criterion, or several criteria....   Here are two sources that might help you accomplish your task with that function.

https://exceljet.net/excel-functions/excel-filter-function

https://www.youtube.com/watch?v=9I9DtFOVPIg

[#3]

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 

[#4]

@Terry_M09 

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.

[#5]

Hi,

I have attached part of the file with no personal identifiable info included.

Column F holds a list of codes some duplicate relating to the amount of duplicate entries in the sheet that code has.

Column G hold asset codes duplicated the number of times the code in F is duplicated.

Column H holds the returned filtered duplicate values which i want to return using XLOOKUP.

The return array uses column Q and R which the duplicate values to be returned.

I hope that makes sense.

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

Thanks.@mathetes 

[#6]

@Terry_M09 

You wrote: I hope that makes sense.

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.

But I suspect that there's a deeper issue regarding the basic database itself.

[#7]

OK Thanks. I may have to look at doing it differently.@mathetes