Using VLOOKUP but ignoring rows with specific text-CodePudding

I have a table which contains a number of room bookings which includes start and end times. I have Column H state if the end time has passed. I would like a VLOOKUP type formula to return the value of Sheet 2 column D on Today's date if column H is not equal to "ENDED".

In the example below i get the 1st incidence of today's booking in AB264 (Can & Venn). Once this session has ended at 10:00 AM i would like to display the next booking (Male Cath).

(For reference B1 = TODAY() and H1 is the room number - both on Sheet 1)

My existing formula returns the 1st match:

=IFERROR(VLOOKUP(B1&"|"&H1,Sheet2!B:G,3,FALSE),"--")

I have a small amount of experience with VBA so if this can be done a better way (maybe even delete the ended rows) then i would be happy to try.

CodePudding user response：

You can use this formula:

=CHOOSECOLS(
     VSTACK(
           tblData[#Headers],
           FILTER(tblData,(tblData[Room]=Room)*(tblData[Date]=Date)*(tblData[Status]=""))),
  4,6,7)

I am using a table for the data (Insert > table) - this allows for more readable formulas :-)

I added two names Date (= your Sheet1!B1) and Room(= your Sheet1!H1)

If you want to returne other/more columns you have to adjust the parameters for CHOOSECOLS at the end of the formula.