Can I use LOOKUP with tables, or is there something else I should be using instead??

[lathamc]

I have a table "TermDates" which is updated with term dates and the corresponding term names:

Academic Year	Term	Start Date	End Date
2019-20	Michaelmas	31/08/2019​	01/01/2020​
2019-20	Lent	31/12/2019​	01/04/2020​
2019-20	Trinity	31/03/2020​	30/09/2020​
2020-21	Michaelmas	31/08/2020​	01/01/2021​

And another series of tables eg. "AnnualSpend20" which includes payment dates:

Item #	Payment Date	Term
123456	23/12/2019	​
123457	01/02/2020	​
123458	14/3/2020	​
123459	15/06/2020	​

I'm looking for a formula to insert in AnnualSpend20[@[Term]] that will lookup the [@[Payment Date]] to see which date range it falls into in TermDates and return the corresponding term name.

This is what I have tried so far, but is returning #N/A error - presumably this formula is only for cell references rather than table references?
=INDEX(TermDates[Term],MATCH(1,IF([@[Payment Date]]>TermDates[Start Date],IF([@[Payment Date]]<TermDates[End Date],1)),0))

Please help and thank you!

 

[Fluff]

Hi & welcome to MrExcel.
That formula should work, although may need array entry depending on your version of Excel.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[mart37]

Close your formula with CTRL+SHIFT+ENTER
or
an alternative formula: =INDEX(TermDates[Term],SUMPRODUCT(([@[Payment Date]]>=TermDates[Start Date])*([@[Payment Date]]<=TermDates[End Date])*(ROW(TermDates[Term])-1)))

 

[Saurabhj]

Hi,

Check below:

Test File.xlsx
	A	B	C	D	E	F
1	Academic Year	Term	Start Date	End Date
2	2019-20	Michaelmas	31-Aug-19	01-Jan-20
3	2019-20	Lent	31-Dec-19	01-Apr-20
4	2019-20	Trinity	31-Mar-20	30-Sep-20
5	2020-21	Michaelmas	31-Aug-20	01-Jan-21
6
7
8
9
10
11
12
13	Item #	Payment Date2	Term
14	123456	23-12-2019	Michaelmas
15	123457	01-02-2020	Lent
16	123458	14-03-2020	Lent
17	123459	15-06-2020	Trinity
18
19
20
21
22
23
Sheet1

Cell Formulas
Range	Formula
C14:C17	C14	=INDEX(TermDates[Term],MATCH(1,([@[Payment Date2]]>=TermDates[Start Date])*([@[Payment Date2]]<=TermDates[End Date]),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[lathamc]

Hi & welcome to MrExcel.
That formula should work, although may need array entry depending on your version of Excel.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you, will do

 

[lathamc]

Close your formula with CTRL+SHIFT+ENTER
or
an alternative formula: =INDEX(TermDates[Term],SUMPRODUCT(([@[Payment Date]]>=TermDates[Start Date])*([@[Payment Date]]<=TermDates[End Date])*(ROW(TermDates[Term])-1)))

Thank you, it was the way I closed the formula, that now works. Thank you so much!