Help - Find the 2nd match using Xlookup

%3CLINGO-SUB%20id%3D%22lingo-sub-2128028%22%20slang%3D%22en-US%22%3EHelp%20-%20Find%20the%202nd%20match%20using%20Xlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2128028%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20way%20using%20XLookup%20to%20return%20the%202nd%20(or%203rd%2C%20or%204th%2C%20or%20whatever)%20match%20in%20the%20lookup%20value%3F%20I'm%20also%20trying%20to%20use%20as%20few%20helper%20columns%20as%20I%20can.%20Otherwise%20I'd%20just%20use%20the%20VLookup%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20task%20is%20to%20look%20through%20purchase%20orders%20and%20create%20a%20pivot%20table%20that%20broke%20down%20how%20much%20of%20each%20material%20was%20used.%20When%20I%20created%20the%20table%20I%20didn't%20know%20that%20there%20could%20be%20multiple%20instances%20of%20the%20same%20material%20in%20each%20purchase%20order.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20an%20example%20of%20what%20I'm%20looking%20at.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOrder%201%26nbsp%3B%20%26nbsp%3BMaterial%201%26nbsp%3B%20%26nbsp%3B6%20Inches%3C%2FP%3E%3CP%3EOrder%201%26nbsp%3B%20%26nbsp%3BMaterial%201%26nbsp%3B%20%26nbsp%3B6%20Inches%3C%2FP%3E%3CP%3EOrder%201%26nbsp%3B%20%26nbsp%3BMaterial%202%26nbsp%3B%20%26nbsp%3B32%20Inches%3C%2FP%3E%3CP%3EOrder%201%26nbsp%3B%20%26nbsp%3BMaterial%203%26nbsp%3B%20%26nbsp%3B41%20Inches%3C%2FP%3E%3CP%3EOrder%202%26nbsp%3B%20%26nbsp%3BMaterial%201%26nbsp%3B%20%26nbsp%3B7%20Inches%3C%2FP%3E%3CP%3EOrder%202%26nbsp%3B%20%26nbsp%3BMaterial%202%26nbsp%3B%20%26nbsp%3B33%20Inches%3C%2FP%3E%3CP%3EOrder%202%26nbsp%3B%20%26nbsp%3BMaterial%202%26nbsp%3B%20%26nbsp%3B33%20Inches%3C%2FP%3E%3CP%3EOrder%202%26nbsp%3B%20%26nbsp%3BMaterial%203%26nbsp%3B%20%26nbsp%3B41%20Inches%3C%2FP%3E%3CP%3EOrder%202%26nbsp%3B%20%26nbsp%3BMaterial%204%26nbsp%3B%20%26nbsp%3B16%20Inches%3C%2FP%3E%3CP%3EOrder%203%26nbsp%3B%20%26nbsp%3BMaterial%201%26nbsp%3B%20%26nbsp%3B6%20Inches%3C%2FP%3E%3CP%3EOrder%203%26nbsp%3B%20%26nbsp%3BMaterial%201%26nbsp%3B%20%26nbsp%3B6%20Inches%3C%2FP%3E%3CP%3EOrder%203%26nbsp%3B%20%26nbsp%3BMaterial%201%26nbsp%3B%20%26nbsp%3B8%20Inches%3C%2FP%3E%3CP%3EOrder%203%26nbsp%3B%20%26nbsp%3BMaterial%202%26nbsp%3B%20%26nbsp%3B19%20Inches%3C%2FP%3E%3CP%3EOrder%203%26nbsp%3B%20%26nbsp%3BMaterial%203%26nbsp%3B%20%26nbsp%3B40%20Inches%3C%2FP%3E%3CP%3EOrder%203%26nbsp%3B%20%26nbsp%3BMaterial%203%26nbsp%3B%20%26nbsp%3B41%20Inches%3C%2FP%3E%3CP%3EOrder%203%26nbsp%3B%20%26nbsp%3BMaterial%204%26nbsp%3B%20%26nbsp%3B18%20Inches%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2128028%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2128830%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20-%20Find%20the%202nd%20match%20using%20Xlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2128830%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F964770%22%20target%3D%22_blank%22%3E%40JMunoz4%3C%2FA%3E%26nbsp%3BAre%20you%20sure%20a%20regular%20pivot%20table%20won't%20work%3F%20See%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20this%20does%20not%20make%20sense%2C%20how%20exactly%20would%20you%20want%20to%20summarise%20the%20data%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2129138%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20-%20Find%20the%202nd%20match%20using%20Xlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2129138%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F964770%22%20target%3D%22_blank%22%3E%40JMunoz4%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20wish%20to%20%3CU%3Elist%3C%2FU%3E%20multiple%20matches%20FILTER%20would%20be%20a%20more%20appropriate%20function%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20FILTER(Orders%5B%5BMaterial%5D%3A%5BSize%5D%5D%2C%20Orders%5BOrder%5D%3DSelected)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EIf%20you%20wish%20to%20aggregate%20similar%20values%20then%20that%20could%20be%20a%20Pivot%20Table%20or%20SUMIFS%2FCOUNTIFS%20combined%20with%20UNIQUE%20to%20identify%20the%20possibilities.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Is there a way using XLookup to return the 2nd (or 3rd, or 4th, or whatever) match in the lookup value? I'm also trying to use as few helper columns as I can. Otherwise I'd just use the VLookup function.

 

My task is to look through purchase orders and create a pivot table that broke down how much of each material was used. When I created the table I didn't know that there could be multiple instances of the same material in each purchase order.

 

Here's an example of what I'm looking at.

 

Order 1   Material 1   6 Inches

Order 1   Material 1   6 Inches

Order 1   Material 2   32 Inches

Order 1   Material 3   41 Inches

Order 2   Material 1   7 Inches

Order 2   Material 2   33 Inches

Order 2   Material 2   33 Inches

Order 2   Material 3   41 Inches

Order 2   Material 4   16 Inches

Order 3   Material 1   6 Inches

Order 3   Material 1   6 Inches

Order 3   Material 1   8 Inches

Order 3   Material 2   19 Inches

Order 3   Material 3   40 Inches

Order 3   Material 3   41 Inches

Order 3   Material 4   18 Inches

3 Replies

@JMunoz4 Are you sure a regular pivot table won't work? See attached.

 

If this does not make sense, how exactly would you want to summarise the data?

@JMunoz4 

If you wish to list multiple matches FILTER would be a more appropriate function

= FILTER(Orders[[Material]:[Size]], Orders[Order]=Selected)

If you wish to aggregate similar values then that could be a Pivot Table or SUMIFS/COUNTIFS combined with UNIQUE to identify the possibilities.

 

@Riny_van_Eekelen 

Thank you. I am still curious if XLOOKUP can return the nth match in an array. But now that you mention it, redoing the pivot table would be the best way to display what I'm looking for. I don't have much experience with pivot tables so I sorta reverse engineered one from a similar worksheet.