Google Sheets VLOOKUP issue when searching data populated by a formula

user_7475003 0
Viewed 26

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!

vlookup formula google-sheets
2 Answers

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 because TODAY() 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.

user_118612180

try:

=FILTER(A2:A; C2:C*1 = TODAY())
user_56326290
Related