Excel 365 XLOOKUP - New User Question

%3CLINGO-SUB%20id%3D%22lingo-sub-2083741%22%20slang%3D%22en-US%22%3EExcel%20365%20XLOOKUP%20-%20New%20User%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2083741%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20the%20XLOOKUP%20function%20to%20retrieve%20duplicate%20values%20from%201%20piece%20of%20criteria.%20The%20formula%20below%20is%20where%20i%20am%20at.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DXLOOKUP(F2%3AF1500%2CQ2%3AQ309%2CQ2%3AR309%2C%2C-1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20up%20a%20code%20number%20as%20criteria%20%2203-01%22%20(1%20of%20many%20in%20a%20column)%3C%2FP%3E%3CP%3EI%20have%20multiple%20duplicate%20matches%20that%20i%20want%20returned%20from%20another%20column%20but%20it%20only%20returns%20the%20first%20match.%20As%20i%20drag%20it%20down%20the%20column%20it%20keeps%20returning%20the%20first%20match.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20i%20get%20it%20to%20return%20all%20matches%20as%20i%20drag%20it%20down%20the%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%3C%2FP%3E%3CP%3ETerry%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2083741%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2083807%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20XLOOKUP%20-%20New%20User%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2083807%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F937986%22%20target%3D%22_blank%22%3E%40Terry_M09%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20might%20be%20better%20served%20by%20the%20FILTER%20function.%20It%20will%20return%20multiple%20rows%20that%20match%20a%20given%20criterion%2C%20or%20several%20criteria....%26nbsp%3B%26nbsp%3B%20Here%20are%20two%20sources%20that%20might%20help%20you%20accomplish%20your%20task%20with%20that%20function.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-filter-function%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-filter-function%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2083843%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20XLOOKUP%20-%20New%20User%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2083843%22%20slang%3D%22en-US%22%3E%3CP%3EThanks.%20I%20am%20using%20the%20filter%20function%20now%20but%20i%20have%20to%20cut%20and%20paste%20down%20my%20sheet%20changing%20the%20criteria%20cell%20reference%20as%20i%20go.%20I%20need%20something%20a%20bit%20easier.%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2083851%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20XLOOKUP%20-%20New%20User%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2083851%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F937986%22%20target%3D%22_blank%22%3E%40Terry_M09%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20post%20a%20copy%20of%20your%20spreadsheet%20then%3F%20I%20think%20we%20need%20a%20bit%20more%20of%20an%20understanding%20of%20the%20full%20context%20here.%20Just%20make%20sure%20there's%20nothing%20confidential%20or%20private%20in%20the%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

6 Replies

@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

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...

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 

@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.

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 

@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.

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