vlookup with date criteria.

[Godders199]

Hello, i am trying to get the following formula to work.
=INDEX('QA Oversight file'!J:J,MATCH(1,IF('QA Oversight file'!G:G, ">="&COA!$M$2,IF('QA Oversight file'!G:G, "<="&COA!$N$2,IF('QA Oversight file'!A:A,"="&A4,1))),0))

Effectively i need to return the text in column J:J, where the date in column G:G is between the dates in M2 and N2 , then the name in column A:A equals A4. all i am able to return is "0".

So Effectively a Vlookup =VLOOKUP(A4,'QA Oversight file'!A:J,10,FALSE) but with the date criteria between M2 and N2

There will only ever be one matching record within the date range.

I have tried both as an array and as a normal formula.

Any help appreciated.

 

[Fluff]

It should be like

=INDEX('QA Oversight file'!J:J,MATCH(1,IF('QA Oversight file'!G:G>=$M$2,IF('QA Oversight file'!G:G<=$N$2,IF('QA Oversight file'!A:A=A4,1))),0))

But with array formula you would be better of limiting the range, rather than using entire columns.

 

[Godders199]

Thanks a lot I thought I had tried every combination , but it works now

 

[Fluff]

You're welcome & thanks for the feedback.