Jan 21 2021 10:31 AM
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
Jan 21 2021 10:45 AM
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
Jan 21 2021 10:55 AM
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
Jan 21 2021 10:58 AM
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.
Jan 21 2021 11:10 AM
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
Jan 21 2021 11:30 AM
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.
Jan 21 2021 11:32 AM
OK Thanks. I may have to look at doing it differently.@mathetes