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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Search Criteria[/TD]
[TD]Book[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]User Input[/TD]
[TD]Book1[/TD]
[TD]01/07/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<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:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Book[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]Book1 V1[/TD]
[TD]01/08/2016[/TD]
[TD]31/07/2017[/TD]
[/TR]
[TR]
[TD]Book1 V2[/TD]
[TD]01/08/2017[/TD]
[TD]31/07/2018[/TD]
[/TR]
[TR]
[TD]Book1 V3[/TD]
[TD]01/08/2018[/TD]
[TD]31/07/2019[/TD]
[/TR]
[TR]
[TD]Book2 V1[/TD]
[TD]01/08/2016[/TD]
[TD]31/07/2017[/TD]
[/TR]
[TR]
[TD]Book2 V2[/TD]
[TD]01/08/2017[/TD]
[TD]31/07/2018[/TD]
[/TR]
</tbody>[/TABLE]

<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!