Excel - Drop down list within a formula

I am sure this is a easy formula but 1 am struggling, I have the following:

On tab 1 I want to enter a colour multiple times into column A using a drop down option, for example and I want to pull the how many information from a table on another sheet, so when I do my formula using xlookup (=XLOOKUP(A2,Sheet2!A2:A7,Sheet2!B2:B7)) it works for the top 4 options but not the rest. Can someone help? I ahve also tried the IF formula etc but with no success.

A B Colours How Many Black 17 Yellow 765 Purple 65 Orange 43 Red #N/A Green #N/A Purple #N/A Orange #N/A

Sheet 2 table:

Colours How Many Red 34 Black 17 Green 32 Yellow 765 Purple 65 Orange 43

I hope this make sense.

Thanks in advance

Wayne