VLOOKUP against a date range and document name based on name and publication date of a book to return the version number of the book

[ayeready]

Hi all.

I'm having a problem trying to code this correctly and i'm looking for some assistance. I'm looking for a user to input a book title along with a date, in order to automatically show the correct version they should be using. For example:

Search Criteria	Book	Date
User Input	Book1	01/07/2018

<tbody>
</tbody>

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">

</code>This should then return a value of Book1 V2 based on the table below:

Book	Start Date	End Date
Book1 V1	01/08/2016	31/07/2017
Book1 V2	01/08/2017	31/07/2018
Book1 V3	01/08/2018	31/07/2019
Book2 V1	01/08/2016	31/07/2017
Book2 V2	01/08/2017	31/07/2018

<tbody>
</tbody>

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">
</code>I've searched for the past 2 weeks and just cannot find the correct formula to get this to work properly.
One of the pitfalls of google learning rather than properly learning excel i'm afraid.

Any help would be greatly appreciated as i'm banging my head against a brick wall just now.

 

[WaterGypsy]

I'd probably 'cheat'.

Name your search fields as S_BOOK & S_DATE, then in a column next to the table enter this formula and copy down :

=IF(AND(LEFT(A2,LEN(S_BOOK))=S_BOOK,B2<=S_DATE,C2>=S_DATE),"Yes","No")

 

[steve the fish]

Heres a possible:

=TRIM(SUBSTITUTE(INDEX(A2:A6,MATCH(1,INDEX((LEFT(A2:A6,LEN(F2))=F2)*(B2:B6<=G2)*(C2:C6>=G2),0),0)),F2,""))

 

[ayeready]

Heres a possible:

=TRIM(SUBSTITUTE(INDEX(A2:A6,MATCH(1,INDEX((LEFT(A2:A6,LEN(F2))=F2)*(B2:B6<=G2)*(C2:C6>=G2),0),0)),F2,""))

Thank you steve the fish. You're a genius!