msquared99
Member
I have a spreadsheet where I need to look up a value based on multiple criteria. One spreadsheet is the master, the other contains the data I need to bring into the master spreadsheet. The master spreadsheet has the Employee ID (Column A),row 1 has the Pay Date such as 9/13/19 (Column I), 9/27/19 (Column J) and so on.
The other spreadsheet has the data I need. Column A has the Employee ID, column B has the Start Date, column C has the End Date, and column D has the Rate I need to return to the master spreadsheet.
So, if the Pay Date is 9/13/19 I need a formula that will return the Rate from the second spreadsheet where the Pay Date falls between the Start and End Dates. Please note that the second spreadsheet does have an end date of 1/1/3000 (This is the so called infinity date).
I tried several ideas but they did not work. Sample file attached. It is one spreadsheet, so treat the two tabs as separate spreadsheets.
The other spreadsheet has the data I need. Column A has the Employee ID, column B has the Start Date, column C has the End Date, and column D has the Rate I need to return to the master spreadsheet.
So, if the Pay Date is 9/13/19 I need a formula that will return the Rate from the second spreadsheet where the Pay Date falls between the Start and End Dates. Please note that the second spreadsheet does have an end date of 1/1/3000 (This is the so called infinity date).
I tried several ideas but they did not work. Sample file attached. It is one spreadsheet, so treat the two tabs as separate spreadsheets.
