xlookup with multiple results

[pete4monc]

Hi all

I have two workbooks one with detailed data (Bought-out-part orders) and one with shortened data (BOP Orders).
In workbook BOP Orders, column A, I have our order numbers and I need the formula to look into Bought-out-part orders column A.
Match our order number and pull in the required data from (Bought-out-part orders).
Problem is some orders have multiple items on the same order, I can get it to work until I have multiple rows with the same order number but different item numbers. The formula returns the first row's details correct but then any duplicate items it returns the first line again?
Any idea how I can over come this?
=XLOOKUP($A3,'[Bought-Out-Part Orders.xlsx]Live'!$A$2:$A$336,'[Bought-Out-Part Orders.xlsx]Live'!$D$2:$D$336,0)

Hope this makes sense to you?

Thanks in advance.
Pete
PS: I am running office 365 but some in my team are running Office 2019.

 

[Fluff]

Does this need to work with xl 2019, as that does not have xlookup?

 

[pete4monc]

Does this need to work with xl 2019, as that does not have xlookup?

Hi Fluff. Yes it must work with 2019.
Thanks

 

[Fluff]

Ok, how about

=INDEX('[Bought-Out-Part Orders.xlsx]Live'!$D$2:$D$336,AGGREGATE(15,6,(ROW('[Bought-Out-Part Orders.xlsx]Live'!$D$2:$D$336)-ROW('[Bought-Out-Part Orders.xlsx]Live'!$D$2)+1)/('[Bought-Out-Part Orders.xlsx]Live'!$A$2:$A$336=$A2),COUNTIFS($A$2:$A2,$A2)))

 

[pete4monc]

Ok, how about

=INDEX('[Bought-Out-Part Orders.xlsx]Live'!$D$2:$D$336,AGGREGATE(15,6,(ROW('[Bought-Out-Part Orders.xlsx]Live'!$D$2:$D$336)-ROW('[Bought-Out-Part Orders.xlsx]Live'!$D$2)+1)/('[Bought-Out-Part Orders.xlsx]Live'!$A$2:$A$336=$A2),COUNTIFS($A$2:$A2,$A2)))

Thanks Fluff, that worked a treat.

I'm still relatively new to vba and formulas on excel, but I found I could read you formula and understand most of it but not the "(15,6," bit in the middle. Could you explain what the "(15,6," is?
Just to make you laugh....... I normally trial everything on my laptop before moving it over to our server. When I used this on the server I had issues, then realised the paths were wrong, what a numpty ....lol.
Thanks for your help.

 

[Fluff]

The 15 tells the aggregate function to operate like the small function & the 6 tells it to ignore errors.

 

[pete4monc]

The 15 tells the aggregate function to operate like the small function & the 6 tells it to ignore errors.

Aah ok... Thank you! I am very new to VBA and each new success is a great feeling! Thanks for your help Fluff

 

[Fluff]

You're welcome & thanks for the feedback.

I am very new to VBA

This isn't VBA it's a formula, the two have nothing to do with each other.