How to recognise a date within a range and then return the cell that is directly beside the result?

raetae

New Member
Hello, I am having some difficulty in using my VLOOKUP FUNCTION.

I have a a 2 column table, column A being a set of dates and column B has names of institutions. I want to have a formula that search for a date that is in a particular quarter and then return the name of the institution that would be in the corresponding column to the right. example may be found below

 29-Sep-13

<tbody>
</tbody>
 Scotia Jamaica Building Society

<tbody>
</tbody>
 29-Sep-13

<tbody>
</tbody>
 Bank of Nova Scotia Ja Ltd.

<tbody>
</tbody>
 19-Nov-13

<tbody>
</tbody>
 RBC Royal Bank

<tbody>
</tbody>
 2-Dec-13

<tbody>
</tbody>
 Portmore Community Development Fund

<tbody>
</tbody>
 3-Dec-13

<tbody>
</tbody>

<tbody>
</tbody>
 12-Dec-13

<tbody>
</tbody>
 Sagicor Bank

<tbody>
</tbody>
 19-Dec-13

<tbody>
</tbody>
 Kris an Charles Investments Co. Ltd.

<tbody>
</tbody>
 10-Jan-14

<tbody>
</tbody>
 Micro Investment Development Agency (MIDA)

<tbody>
</tbody>

<tbody>
</tbody>

Therefore if i were to have a list populated with the institutions that signed a contract between January 2014 and March 31, 2014. then that list should have one entity "Micro Investment Development Agency (MIDA).

If the formula is set to look for date between September 2013 and December 31, 2013, then all other entities would populate the list.

In lay mans terms, i need to a formula that will create a list of institutions out of column 2 above, IF the dates in column 1 fall in a particular range. I tried using a vlookup function like this:

VLOOKUP( A:A>=1/1/2014 <= 31/03/2014, A1:B6, 2, FALSE)
The problems is that the first argument "lookup value" seems to have to be an actual figure or cell. What i want is that the lookup value be determined through another formula and then that value is used to search the original table and return the value of the cell to the right.

e.g the lookup value should be determined by searching the column 1 for dates between a particular specified range and then return the values of the cells to the right of the lookups. Therefore the formula should be able to determine that 10 January 2013 falls between the specified range then return the value in column 2 (Micro Investment Development Agency (MIDA))

I know there is a way of doing this, can anyone help.

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

AhoyNC

Well-known Member

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,659
Messages
5,838,645
Members
430,558
Latest member
Krampus

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.

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

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