Excel: assistance with nested formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2263060%22%20slang%3D%22en-US%22%3EExcel%3A%20assistance%20with%20nested%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2263060%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EWindows%2010%2F%3C%2FSPAN%3E%3CSPAN%3EEXCEL%20FOR%20MICROSOFT%20365%20MSO%20(16.0.13901.20148)%2032-BIT%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20have%20attached%20my%20file.%26nbsp%3B%20Here's%20what%20I'm%20trying%20to%20make%20happen.%3C%2FP%3E%3CUL%3E%3CLI%3EThe%20user%20to%20put%20an%20x%20in%20the%20correct%20cell%20in%20the%20%3CSTRONG%3ESelect%20Pay%20Date%20(F)%3C%2FSTRONG%3E%2C%26nbsp%3Bcorresponding%20to%20the%20current%20pay%20date.%3C%2FLI%3E%3CLI%3EBased%20on%20the%20specific%20cell%20in%20column%20F%2C%20I%20want%20a%20formula%20that%20pulls%20the%20correct%20date%20from%20want%26nbsp%3B%3CSTRONG%3ECheck%20Date%2FPay%20Day%20(G)%3C%2FSTRONG%3E%20and%20put%20it%20in%20%3CSTRONG%3EE1%3C%2FSTRONG%3E%20in%20short%20date%20format.%3C%2FLI%3E%3CLI%3EBased%20on%20the%20specific%20cell%20in%20column%20F%2C%20I%20want%20a%20formula%20that%20pulls%20the%20correct%20date%20from%20want%20%3CSTRONG%3EPeriod%20Start%26nbsp%3B%3C%2FSTRONG%3E%3CSTRONG%3E(I)%3C%2FSTRONG%3E%20and%20put%20it%20in%20%3CSTRONG%3EB1%3C%2FSTRONG%3E%20in%20short%20date%20format.%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI've%20tried%20IF%2C%20COUNTIF%2C%20in%20conjunction%20with%20INDEX%20and%20MATCH%20and%20I%20cannot%20seem%20to%20figure%20it%20out.%26nbsp%3B%20I%20feel%20like%20this%20is%20possible.%26nbsp%3B%20I%20would%20really%20appreciate%20some%20assistance%20in%20figuring%20out%20the%20proper%20formula.%26nbsp%3B%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2263060%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2263062%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3A%20assistance%20with%20nested%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2263062%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1021248%22%20target%3D%22_blank%22%3E%40EDDRENB%3C%2FA%3E%26nbsp%3BPlease%20see%20attached.%20Used%20INDEX%2FMATCH%20in%20B1%20and%20E1.%20Is%20that%20what%20you%20meant%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEdit%3A%20Noticed%20you%20had%20Data%20Validation%20in%20F%2C%20accepting%20only%20%221%22%2C%20rather%20than%20%22x%22.%20Changed%20the%20formulae%20accordingly.%20New%20file%20uploaded%20below.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Windows 10/EXCEL FOR MICROSOFT 365 MSO (16.0.13901.20148) 32-BIT

I have attached my file.  Here's what I'm trying to make happen.

  • The user to put an x in the correct cell in the Select Pay Date (F), corresponding to the current pay date.
  • Based on the specific cell in column F, I want a formula that pulls the correct date from want Check Date/Pay Day (G) and put it in E1 in short date format.
  • Based on the specific cell in column F, I want a formula that pulls the correct date from want Period Start (I) and put it in B1 in short date format.

I've tried IF, COUNTIF, in conjunction with INDEX and MATCH and I cannot seem to figure it out.  I feel like this is possible.  I would really appreciate some assistance in figuring out the proper formula.  Thank you!

5 Replies

@EDDRENB Please see attached. Used INDEX/MATCH in B1 and E1. Is that what you meant?

 

Edit: Noticed you had Data Validation in F, accepting only "1", rather than "x". Changed the formulae accordingly. New file uploaded below.

OMG!! Thank you so much I have been pulling my hair out and you did it in like two minutes.
So does it have to be numbers in the formula instead of x? Just curious for next time.

@EDDRENB Well, the data validation is set-up to accept only one entry in F4:F19 and then only "1" (i.e. the number 1 as a text) or "" (i.e. a blank).

 

@Riny_van_Eekelen Got it!  I originally had it as x but then i thought maybe it wasn't working because it needed to be numeric.  Thank you so much again.