Array Formula pulling first line of data for all scenarios

[legendaryjacks]

Lot Number Customer Number Buyer Number Price Description Buyer Number
1 0005 53 800 a 56
2 0008 56 200 b 800
3 0009 59 300 c


here is what I have in the last column I have entered 56 and the the number right below should pull the prices and items of what this customer paid for. My formula is returning the information for lot 1 for all scenarios, however subsequent information is correct, meaning the if they bought more than one lot number that is information is correct. Here is my formula where the 800 is pulling, for exampel buyer number 56.

IF(ROWS(N$16:N16)<=COUNTIF(J$16:J$100,M$16),INDEX(K$16:K$24,SMALL(IF(J$16:J$24=M$16,ROW(J$16:J$24)-ROW(J$16)+1),ROWS(N$16:N16))),"")


please help!

[FDibbins]

Hi and welcome to the forum

Often, when you copy your data here, it looses the layout/formatting you have, and its hard to see what you have.

Please upload a sample workbook with your dummy data
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Tony Valko]

Did you array enter the formula?

Array formulas are entered differently than a regular formula.
After you type in a regular formula you hit the ENTER key.
With an array formula you *must* use a combination of keys.
Those keys are the CTRL key, the SHIFT key and the ENTER key.
That is, hold down both the CTRL key and the SHIFT key then
hit the ENTER key.

When done properly Excel will enclose the formula in squiggly
brackets { }. You can't just type these brackets in, you
*must* use the key combo to produce them. Also, anytime
you edit an array formula it *must* be re-entered as an
array using the key combo.

[legendaryjacks]

thank you this did the trick

[Tony Valko]

Good deal. Thanks for the feedback!