best way to look up and return next column value ( rank )

hi guys

Im trying to find out the rank value depending on two cells and returning the rank value ( next column to the answer )

in the example i have used Arsenal and 442

so for home shots it needs to look in column a for "arsenal" then look for "442" between cells D1 and AU1 )
i then need it to return the answer in the rank column to the right which would be cell Y2

then

for home shots on target it needs to look in column a for "arsenal" then look for "442" between cells AX1 and CO1 )
i then need it to return the answer in the rank column to the right which would be cell BS2

what would be the best way to go about this please

I welcome any help

wow
thanks so much Richard Works a treat

Hi Richard

Sorry to trouble you
i have tried to extend my data but cannot understand how your formula works so that i can extend with it
Would you please be kind enough to look at the attachment again for me and explain how the formula works and how i can adapt it to use the next sector and the ideal scenario underneath.

really appreciate your help
kind regards

Hi Richard

Sorry i havent got back to you sooner but i have been unwell.
Thank you for your efforts
unfortunately when i select another team in cell B2 i get #REF! in the returning cells

really appreciate your help

you sir are an absolute legend.
I really cannot thank you enough and thank you for your patience