VLOOKUP with date range does not work as intended

0 votes

I have two excel tables in separate sheets:

table1:

Site_ID ....... Visit_Date
--------------------------
AS01            12-Feb-23
ES96            10-Jan-23   
GH15            14-Mar-23
AS01            26-Mar-23
VD10            08-Apr-23
LS18            14-Jan-23
GH15            26-Mar-23

table2:

            01-Feb-23   01-Mar-23   01-Apr-23
            28-Feb-23   31-Mar-23   30-Apr-23
Site ID         FEB23       MAR23       APR23
---------------------------------------------
AS01
ES96
GH15
VD10
LS18

If the date falls within the acceptable range indicated above the table2 heading, I want to look for the Site ID in table1 and insert Visit Date in table2.

I tried the following formula:

=VLOOKUP(table2[@[Site ID]:[Site ID]],IF((table1[[Visit_Date]:[Visit_Date]]>=E$1)*(table1[[Visit_Date]:[Visit_Date]]<=E$2),table1[[Site_ID]:[Visit_Date]],""),12,FALSE)

Unfortunately, the FEB23 field produces dates that are out of range, and the remaining columns yield value errors. Why am I misusing this?

Apr 11, 2023 in Others by Kithuzzz
• 38,010 points
459 views

No answer to this question. Be the first to respond.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.

Related Questions In Others

0 votes
1 answer

Flutter Error: MediaQuery.of() called with a context that does not contain a MediaQuery.

Hi@akhtar, You need a MaterialApp or a WidgetsApp around your widget. They ...READ MORE

answered Aug 27, 2020 in Others by MD
• 95,440 points
4,843 views
0 votes
1 answer
0 votes
1 answer

Why does z-index not work?

The z-index property only works on elements ...READ MORE

answered Feb 18, 2022 in Others by Rahul
• 9,670 points
391 views
0 votes
1 answer

JavaScript API does not work for Excel 2013?

Each method in the Office.js APIs is ...READ MORE

answered Sep 26, 2022 in Others by narikkadan
• 63,420 points
403 views
0 votes
1 answer

'Microsoft.Office.Interop.Excel.Range' does not contain a definition for 'get_Default'

You are using C# version 4, the ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 63,420 points
1,736 views
0 votes
1 answer

Can an Excel xll function indicate that the return value should be displayed as a date and not a number?

To my knowledge, the only method to ...READ MORE

answered Jan 5, 2023 in Others by narikkadan
• 63,420 points
513 views
0 votes
1 answer

Using Excel VLOOKUP() function across two sheets

The syntax for VLOOKUP is VLOOKUP(Lookup_Value,Table Array,Col_index_num,Range_lookup) OR, to start in ...READ MORE

answered Sep 30, 2022 in Others by narikkadan
• 63,420 points
767 views
0 votes
1 answer

Convert three letter country codes to full country names

Just create a list to be used in ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 63,420 points
585 views
0 votes
1 answer

How to categorize/classify numbers from different ranges using Excel?

I wouldn't advise utilizing nested IFs because ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 63,420 points
2,923 views
0 votes
1 answer

Using VLOOKUP()

Vlookup takes the lookup value first, so ...READ MORE

answered Nov 5, 2022 in Others by narikkadan
• 63,420 points
350 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP