Can't use a standard vlookup on this data set....

I have a listing of data which has a column with 'entry number' on it. Call this list 1.

I then have another listing of data (list 2) which has a date column that I want to get into the 'list 1' data. However instead of having an 'entry number' column that i can do a look up on this has 'entry number from' and 'entry number to' columns. Therefore an entry number on list one may be included within one row of data on the list 2 but will be within the range of the from and to cells.

Is there any way of returning a date from list 2 to match the entry numbers on list 1?

Any help would be very much appreciated.

Thank you

E.g. list 1

Type GL name Entry
SALES Sales Ledger 85101


E.g. list 2
No. Creation Date Source Code Journal Batch Name From Entry No. To Entry No. From VAT Entry No. To VAT Entry No.
11160 02/01/2014 PURCHASES 83613 83614 10796 10796

Sample workbook...

Brilliant - thank you very much.

In C2 of List1, then drag down

Please Login or Register  to view this content.

kvsrinivasamurthy - In this case I've tried to avoid SUMPRODUCT as there are thousands of rows which needs to be recalculated every time you change content of some cells and this take computer resources and time, thus I came up with simple INDEX-Match...