Pull data for the invoice Using Xlookup()

[mazher]

Hi Excel Gurus,

I am using the following formula to pull the data using XLOOKUP()
=XLOOKUP(AL3,AB9:AB15,AB9:AE15)

Its works fine when for data with one row

Where the data comprises over two or more rows it shows the first row.

Any suggestions/help will be much appreciated.

I have uploaded the photos for sample data , hope it helps

Thanks in advance

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=Filter(AB9:AE15,AB9:AB15=AL3)

 

[mazher]

Extremely thankful @Fluff

You are a star.

Any suggestions on how to split the data that is being spilled as I have just pasted a sample data, actually there are more columns?

 

[Fluff]

What do you mean to split the data?

 

[mazher]

Sorry for my confusion and thanks for your help again.

I managed to pull the rest of the data using XLOOKUP()
Previously I was using VLOOKUP and INDEX/MATCH and array formulas

 

[Fluff]

Glad you sorted it & thanks for the feedback.