Index match if "date" is between 2 dates in 2 seperate columns.

I am trying to match a number in one spreadsheet against a number in another spreadsheet and extract the name which sits a few columns before said number.

this issue is I only want to extract the name against this value if the date from the first spreadsheet falls in between two date (each in different columns) on the 2nd spread sheet.

this is how far i've got but the formula just keeps returning False.

=IF(AND(A1822>='Car Renter Transactions'!K:K,A1822<='Car Renter Transactions'!M:M),INDEX('Car Renter Transactions'!S:S,MATCH(H1822,'Car Renter Transactions'!W:W,0),FALSE))

Is the formula the wrong way round? does it need to be =if(index? of does this if and statement not work because i am searching against the entire column where as each part of the formula kind of relies on the other part before it can process?

Help?

Hi Benjiboi_87,

If what you are looking to do is return the value in Car Rental Transactions:Column S from the same row in Car Rental Transactions:Column W that matches the value in H1822 when the date in A1822 is greater than all the dates in Car Rental Transactions:Column K and is less than all the dates in Car Rental Transactions:Column M, than it works based on my check.

Thanks,

Dan

Thanks Teylen,

Please see attached.

What Dan said below made sense and when i rand the data in test sheet i managed to get a result but it is only returning a result based on the same line item in the other sheet.. If this makes sense?

Cheers

Thanks Tom,

When running a test spreadsheet with same formula i was able to get it to work but only if the row number match on each spreadsheet. i.e. if running the formula from row A in spreadsheet 2 and the criteria for dates and Value were matching that required with the data in row 'A' of spreadsheet 1 then all is good. however if the data that met the criteria was in any other row on spreadsheet 1 then the result would not be returned.

Cheers

Thanks JeteMC,

This works however it is still displaying the name based on rows. i.e. row 2 shows the name from data tab row 2, row 3 from row 3. so on and so fourth. It is not displaying the name based on the etag number match between the required dates?

I feel we are getting closer though

Thanks JeteMC,

This works however it is still displaying the name based on rows. i.e. row 2 shows the name from data tab row 2, row 3 from row 3. so on and so fourth. It is not displaying the name based on the etag number match between the required dates?

I feel we are getting closer though

Just realised that previous formula still returns name even if date and tag number don't match, All its seems to do is return the name value?

Just realised it was an array formula… Still not working though.. only one result was turned up with a name and it was the wrong name. and when i changed the tag number it had no effect on the result?

Try

=IFERROR(INDEX(Data!I:I,MATCH(1,(Data!M:M=$H2)*($A2>=Data!A:A)*($A2<=Data!C:C),0)),"")

enter with Ctrl+Shift+Enter

As previously posted, it would help you added expected results to your posted file.

Non-array version of my previous post:

=IFERROR(INDEX(Data!$I:$I,MATCH(1,INDEX((Data!$M:$M=$H2)*($A2>=Data!$A:$A)*($A2<=Data!$C:$C),0),0)),"")

John Topley you are a genius!

Ok still having issues with the formula? have transferred it to the main spreadsheet as is and it is only working for the first cell.. from then on it shows blank even when everything matches. this applies to both formulas John Topley & JeteMC. you formulas both work in the test environment but only for the first cell in the live environment.

Here is the formula from the live environment on a cell that is show blank when it shouldn't be.
=ArrayFormula(IFERROR(INDEX('Car Renter Transactions'!S:S,MATCH(1,('Car Renter Transactions'!W:W=H584)*(A584>='Car Renter Transactions'!K:K)*(A584<='Car Renter Transactions'!M:M),0)),""))

It only shows = array formula because the formula is in google sheets. Is there anything else I should check at my end. All cells are reflecting the right data type and all dates are in matching formats.

I have never used Google sheets: do ArrayFormula have to be entered with Ctrl+SHift+Enter as for Excel?

Have you tried the non-array version:

=IFERROR(INDEX(Data!$I:$I,MATCH(1,INDEX((Data!$M:$M=$H2)*($A2>=Data!$A:$A)*($A2<=Data!$C:$C),0),0)),"")

Disregard figured it out thank you all