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

lathamc

New Member
Joined
Feb 17, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
I have a table "TermDates" which is updated with term dates and the corresponding term names:
Academic YearTermStart DateEnd Date
2019-20Michaelmas
31/08/2019​
01/01/2020​
2019-20Lent
31/12/2019​
01/04/2020​
2019-20Trinity
31/03/2020​
30/09/2020​
2020-21Michaelmas
31/08/2020​
01/01/2021​

And another series of tables eg. "AnnualSpend20" which includes payment dates:
Item #Payment DateTerm
12345623/12/2019
12345701/02/2020
12345814/3/2020
12345915/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!
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,517
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Aug 4, 2017
Messages
1,059
Office Version
  1. 2016
Platform
  1. Windows
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)))
 
Solution

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

Check below:

Test File.xlsx
ABCDEF
1Academic YearTermStart DateEnd Date
22019-20Michaelmas31-Aug-1901-Jan-20
32019-20Lent31-Dec-1901-Apr-20
42019-20Trinity31-Mar-2030-Sep-20
52020-21Michaelmas31-Aug-2001-Jan-21
6
7
8
9
10
11
12
13Item #Payment Date2Term
1412345623-12-2019Michaelmas
1512345701-02-2020Lent
1612345814-03-2020Lent
1712345915-06-2020Trinity
18
19
20
21
22
23
Sheet1
Cell Formulas
RangeFormula
C14:C17C14=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

New Member
Joined
Feb 17, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Feb 17, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,505
Messages
5,625,205
Members
416,080
Latest member
blemon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top