搜索由公式填充的数据时的Google表格vlookup问题
嘿，对不起，是否以前问过。我已经搜索了高低，但在任何地方都找不到答案。
我正在使用Google表来存储与酒店房间有关的数据。我基本上有3列由于其他公式而填充。在这些列中，我有以下内容：
| 室内 | 检查日期 | 检查日期 |
|---|---|---|
| 室1 2 | 28/06/2022 | 29/06/2022 |
| 室2 | 29/06/2022 | 30/06/2022 |
现在将这些数据带入这些列中，以下公式：
=INDEX(QUERY(TO_TEXT({Master!A2:D})))
请注意！是同一工作簿中的另一张纸。
所有这些都可以。
问题是，现在我将所有数据都放在一个地方，我想创建另一个视图，这只会向我展示今天的退房房间。因此，我使用以下公式：
=Filter($A$2:$A, $C$2:$C = Today())
当我在相同的数据上使用此公式时，该公式仅使用值粘贴到示例表中（基本上没有任何公式），它可以完美地工作。但是，当我在上方使用=过滤器公式并将其指向A：A和C：C时，它不会返回任何匹配。
就像公式是正确的，但公式无法搜索从另一个公式填充的数据。 帮助！
Hey sorry if this has been asked before. I have searched high and low but cannot find the answer anywhere.
I am using google sheets to store and query data related to Hotel rooms. I basically have 3 columns which are populated as a result of other formulas. In these columns I have the following:
| Room Number | Check In Date | Check out date |
|---|---|---|
| Room 1 | 28/06/2022 | 29/06/2022 |
| Room 2 | 29/06/2022 | 30/06/2022 |
Now this data is brought into these columns with the following formula:
=INDEX(QUERY(TO_TEXT({Master!A2:D})))
Note that Master! is another sheet in the same workbook.
All of this works fine.
The issue is that now I have all the data in one place, I want to create another view which only shows me Rooms that have a check out for today. So to do that I use the following formula:
=Filter($A$2:$A, $C$2:$C = Today())
When I use this formula on the same data which has been pasted into a sample sheet using values only, (so essentially without any formulas) it works perfectly. But when I use the =Filter formula above and point it at A:A and C:C it does not return any matches.
It's like the formulas is correct but the formula cannot search data which has been populated from another formula.
Help!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我认为您可能遇到的问题是，当您从另一个表中摘取数据时，您将每个值设置为文本（带有
to_text（）函数）。过滤器不起作用，因为today（）不返回文本值，因此在该列中不匹配任何内容。一个解决方案将是然后也将
today（）转换为这样的文本：= filter（$ a $ 2：$ a，$ c $ 2：$ c = $ c = text（today（loday）， “ dd/mm/yyyy”））我刚刚在我制作的快速纸上对此进行了测试，并且最初与您遇到了相同的问题（尽管没有从公式中提取信息）。您可能在测试表中取得了成功，因为通过粘贴该值，它们会自动转换为日期格式，而不是文本。一旦我调整了您的公式以匹配上面列出的公式，该过滤器对我有用。
如果您对此有任何问题，请告诉我。
I think the issue you may be having is that when you are pulling that data from another sheet you are setting each value to text (with the
TO_TEXT()function). The filter is not working becauseTODAY()does not return a text value, and therefore does not match anything in that column.One solution would to be to then also convert
TODAY()into text like this:=Filter($A$2:$A, $C$2:$C = text(TODAY(),"dd/mm/yyyy"))I just tested this on a quick sheet I made, and originally had the same issue as you (despite not pulling the information from a formula). You probably had success with the test sheet because by pasting the values there, they were automatically converted into a date format, instead of text. Once I adjusted your formula to match the one listed above, the filter worked for me.
Please let me know if you have any issues with this.
尝试：
try: