SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2737288%22%20slang%3D%22en-US%22%3EAuto-populate%20B1%20with%20data%20from%20another%20sheet%20based%20on%20pull%20down%20selected%20for%20A1%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2737288%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20set%20up%20one%20cell%2C%20say%20A1%2C%20with%20a%20pull%20down%20to%20select%20the%20first%20day%20of%20a%20calendar%20week.%3C%2FP%3E%3CP%3EI%20would%20like%20B1%20to%20automatically%20populate%20with%20the%20applicable%20week%20%23%20that%20corresponds%20to%20that%20which%20I%20have%20listed%20in%20a%20column%20on%20sheet%202%20for%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eie.%26nbsp%3B%20A1%20pulldown%2C%20and%20select%20%3CSTRONG%3ESept%206%2C%202021%3C%2FSTRONG%3E%20(C39%20on%20sheet%202).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BB2%20I%20would%20like%20to%20auto-populate%20%3CSTRONG%3E36%3C%2FSTRONG%3E%20for%20the%20week%20%23%20(B39%20on%20sheet%202)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2737288%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2737396%22%20slang%3D%22en-US%22%3ERe%3A%20Auto-populate%20B1%20with%20data%20from%20another%20sheet%20based%20on%20pull%20down%20selected%20for%20A1%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2737396%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1151348%22%20target%3D%22_blank%22%3E%40sherrymr%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20B1%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFERROR(INDEX(Sheet2!B%3AB%2CMATCH(A1%2CSheet2!C%3AC%2C0))%2C%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20Microsoft%20365%2C%20you%20can%20also%20use%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DXLOOKUP(A1%2CSheet2!C%3AC%2CSheet2!B%3AB%2C%22%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

 

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 Replies
best response confirmed by allyreckerman (Microsoft)
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,"")

 

 

XLOOKUP worked perfect, thanks for the quick response!