Need to copy 9 cells to another worksheet when barcode is scanned

Hi,

I am very new to this forum (and Excel as well) and thank you in advance for any support you can get me

Here is the situation:
I have a worksheet called ORDER where I have pallets and their contents: 1 barcode per pallet and 9 barcodes per box (9 boxes on a pallet).
Capture.JPG
On another worksheet called DESPATCH, I am trying to get a formula so when I enter the pallet barcode *p2* for instance in a cell. it automatically shows the 9 boxes details (without having to scan each box again).

Any ideas how to solv this?

Thanks folks for your help !

Momo

just enter the pallet no.
and get the whole record..
check the attachment...


click * to appreciate..

Thank you so much Vikas, that's great!
***

Hi Vikas,
sorry but I have noticed that the formula does not work beyon 9 pallets. if I have a pallet 10 it will just return the value for Pallet 1. I guess it is down to the way it indexes pallet 1, 10,11 etc will return the pallet 1 value
likewise pallet 2, 20,,22 etc will return pallet 2 value.
Is there anything I can add to the formula so it differentiate pallets with similar digits?

many thanks for your help!