how do you make index&match work in google sheets with dates

[Ronderbecke]

I have an excel document where I have a function working just fine but I cannot get it to work in google sheets. I can't seem to figure out why it won't work properly, but I feel like it is because its using dates. I have tried using text() and indirect() functions along with formatting the columns to show as plain text and nothing works. Here is currently the function working in excel:
=INDEX($A$2:$A$10,MATCH(B3,$A$2:$A$10,1))

8/1/2021​	8/12/2021	8/1/2021​
9/17/2021​	9/5/2021
12/13/2021​
1/17/2022​

It pulls perfectly but gives in google sheets in always just displays the last date in the row? Do I have to use a different function?

 

[ExceLoki]

when i use your formula in gsheets, it works correctly. be sure your dates are formatted as dates not text

8/1/2021	8/12/2021		8/1/2021
9/17/2021	9/5/2021		8/1/2021
12/13/2021	1/1/2022		12/13/2021
1/17/2022	2/1/2022		1/17/2022

 

[Ronderbecke]

when i use your formula in gsheets, it works correctly. be sure your dates are formatted as dates not text

8/1/2021	8/12/2021		8/1/2021
9/17/2021	9/5/2021		8/1/2021
12/13/2021	1/1/2022		12/13/2021
1/17/2022	2/1/2022		1/17/2022

strange, when i uploaded the document, it worked on the formula in google sheets, but broke the formula for referencing? I am using:
=Vlookup(D3,INDIRECT(indirect("C2")&"!$A$2:$B"),2,0) and now it gives an error saying:
ErrorFunction INDIRECT parameter 1 value is '44409!$A$2:$B'. It is not a valid cell/range reference. that function was working before they were cells that were dates?

 

[Ronderbecke]

strange, when i uploaded the document, it worked on the formula in google sheets, but broke the formula for referencing? I am using:
=Vlookup(D3,INDIRECT(indirect("C2")&"!$A$2:$B"),2,0) and now it gives an error saying:
ErrorFunction INDIRECT parameter 1 value is '44409!$A$2:$B'. It is not a valid cell/range reference. that function was working before they were cells that were dates?

Sorry, fixed it by changing the output!
=Vlookup(D3,INDIRECT(text(C2,"m/d/yyyy")&"!$A$2:$B"),2,0)