VLookup with If statement (?)

[DebraC]

Hi all,

I need assistance with how to set up an Excel formula that allows me to take a hire date and use it to find the pay date that is closest yet exceeds the hire date (i.e. future) and returns a remaining number of pay cycles number from an adjacent column, so that we can calculate offer letter PTO dates for the hire year.

So in B17, I want to return the number of remaining pay periods in the year from column B29 (which goes to 26 pay periods, but it's cut off in the snippet below).

Here is a snippet from my table:

Thank you!

 

[Trebor76]

Hi DebraC,

Welcome to MrExcel!!

Assuming the "Hire Date" range is A29:A39 and the "Pay Pd" range is B29:B39 (amend the following to suit if not) try this array formula* in cell B17:

{=MIN(IF($A$28:$A$39>$B$13,$B$28:$B$39))}

Regards,

Robert

* You need to press Ctrl+Shift+Enter to complete (enter) an array formula. Do not just add the curly brackets manually.
See here for more details.

 

[Peter_SSs]

* You need to press Ctrl+Shift+Enter to complete (enter) an array formula. Do not just add the curly brackets manually.

@Trebor76
The OP has Excel 365 so formulas do not require array entry

@DebraC
Welcome to the MrExcel board!

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. You will generally get faster/better responses.

So in B17, I want to return the number of remaining pay periods in the year from column B29 (which goes to 26 pay periods,

Like this?

22 11 30.xlsm
	A	B
13		1/03/2023
14
15
16
17		22
18
27
28	1/01/2023
29	6/01/2023	1
30	20/01/2023	2
31	3/02/2023	3
32	17/02/2023	4
33	3/03/2023	5
34	17/03/2023	6
35	31/03/2023	7
36	14/04/2023	8
37	28/04/2023	9
38	12/05/2023	10
39	26/05/2023	11
40	9/06/2023	12
41	23/06/2023	13
42	7/07/2023	14
43	21/07/2023	15
44	4/08/2023	16
45	18/08/2023	17
46	1/09/2023	18
47	15/09/2023	19
48	29/09/2023	20
49	13/10/2023	21
50	27/10/2023	22
51	10/11/2023	23
52	24/11/2023	24
53	8/12/2023	25
54	22/12/2023	26
Pay periods

Cell Formulas
Range	Formula
B17	B17	=COUNTIF(A29:A54,">"&B13)

 

[Trebor76]

The OP has Excel 365 so formulas do not require array entry

Thanks Peter. So 365 is smart enough to set the formula as an array? So I take it that that means they're not as memory hungry as in previous versions?

 

[Peter_SSs]

Thanks Peter. So 365 is smart enough to set the formula as an array? So I take it that that means they're not as memory hungry as in previous versions?

365 tries to evaluate all formulas as array formulas. I'm not sure about the effect on memory usage, but practically it is much easier not having to remember C+S+E.