Auto-populate B1 with data from another sheet based on pull down selected for A1

[sherrymr]

I have set up one cell, say A1, with a pull down to select the first day of a calendar week.

I would like B1 to automatically populate with the applicable week # that corresponds to that which I have listed in a column on sheet 2 for reference.

ie.  A1 pulldown, and select Sept 6, 2021 (C39 on sheet 2). 

 B2 I would like to auto-populate 36 for the week # (B39 on sheet 2)

[#2 · Accepted solution]

@sherrymr 

In B1:

=IFERROR(INDEX(Sheet2!B:B,MATCH(A1,Sheet2!C:C,0)),"")

If you have Microsoft 365, you can also use

=XLOOKUP(A1,Sheet2!C:C,Sheet2!B:B,"")

[#3]

XLOOKUP worked perfect, thanks for the quick response!