Match multiple criteria, within range on 1 criteria

[dbrice]

Could someone tell me why this is not working?
Below is a screenshot:

I have 1 tab that has all my crime data,
I have another tab that I have set up to use as a way of querying the data ("Address Summary").
On Address Summary, the user enters the starting address (C5) and ending address (C6), and the date range (D7 and G7). I split up the address and get: the number of the starting address (L6), the number of the ending address (L7), and the street name (H6).
K4 contains the first match in the data (arranged descending by date) for the ending date (2/14/17).
I am trying to use indirect & match to auto populate the corresponding data, in this case the address returned should be within the range.
This is the formula in question in cell P39:
=INDEX('Crime Data'!B:F, MATCH("*"&$H$6&"*",IF(TEXT(INDIRECT("'Crime Data'!AG"&K4&":AG30000"),0)>=TEXT($L$6,0),IF(TEXT(INDIRECT("'Crime Data'!AG"&K4&":AG30000"),0)<=TEXT($L$7,0),INDIRECT("'Crime Data'!AH"&K4&":AH30000"))),0)+$K$4-1,3)
AG in Crime Data contains the street number and AH the street name. Crime Data B:F contains all the data I want to return.
But it returns an address outside the range but for the correct street name. Suggestions please...

 

[AliGW]

Nothing to see ...

 

[dbrice]

How do I insert a picture?

 

[AliGW]

There is no attachment facility. You'd have to link to a file-sharing site.

 

[Joe4]

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.

Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[dbrice]