Match Value based on corresponding dates

I have the spreadsheet attached.

I'd like to find Client No from lookup sheet based on the date provided in the live sheet.

The same client can appear with a different client number, so i need to lookup the name and date (from live sheet) and find the corresponding client number in the lookup sheet where the date from live sheet falls between the 2 dates on the lookup sheet.

I hope this makes sense. Any help appreciated. Thank you

This might do what you're looking for.

=IFERROR( 
  QUERY(SORT(FILTER(Lookup!A$2:D,Lookup!C$2:C=B2,Lookup!A$2:A<=A2),1,0), 
    "SELECT * WHERE COL4 >= DATE '"&TEXT(A2,"YYYY-MM-DD")&"' LIMIT 1",0),
  QUERY(SORT(FILTER(Lookup!A$2:D,Lookup!C$2:C=B2,Lookup!A$2:A<=A2),1,0),
    "SELECT * LIMIT 1",0) )

I've added a tab Live-GK to your sheet, with this formula in C2. It has to be dragged down. There may be another approach where it can be done as an arrayformula, but I haven't figured that out.

Note that on my tab, I'm doing the lookups from Lookup-GK, since I could add more test data there. The above formula can be used as is, pasted into cell C2 in your Live tab.

Note that for debugging purposes, column H of my tab returns all of the columns, not just the client #, so the start and end dates can be verified.

Explanation:

The inner filter selects all rows from the Lookup tab where: i) the client name (column C in Lookup) matches the client name in column B (of Live), and, ii) the start date (column A in Lookup) is less than or equal the client date in Live.

These records are sorted in descending date order.

Then the query selects the first record where the end date (column D in Lookup) is greater than the client date in Live.

If the Lookup record has no end date, this gives an error (empty query result) so IFERROR, a second query is run, but without the filtering by end date, selecting the one record with no end date, but an appropriate start date.

These seemed to work with the few test records I used. If there is a duplication of client dates, the first client # is returned. See client #1 and #7 in my test data. Some more error handling might be necessary if your client records might have overlapping date ranges, as CalculusWhiz asked.