Return values from another table between dates and specific text

MATCH and INDEX should work for the example provided.

LOOKUP will always accept a 'close match', defined as the largest value less than or equal to the search-for value.

VLOOKUP, or in this case, HLOOKUP can be set to accept a 'close match; or demand an 'exact match,' but this won't help here, where the search-for value in the first of a month, and the expected match is the 20th of the same month.

Enter MATCH, the most flexible of the search functions.

Match has three settings: Find Largest—which is the same as 'close match' in the functions above, 'Find value'—effectively the same as 'Exact match', and Find Smallest, which searches for the 'smallest value greater than or equal to' the search-for value.

MATCH searches a range for the best value to fit that description, and reurns a number indicating the position of that value in the list contained in the specified range.

Working with INDEX, MATCH can return that number as the Column-index, and Index will return the value in that column of the specified row.

The "I want…" pointer is to cell C6 of "Table 1". For this cell, the statements below apply:

The search-for value to determine the row from which the data is to be returned is in cell B6 of Table 1.

The best match is to be found in column B of Table 2.

The search-for value to determine the column is the date in C1 of Table 1.

The best match is to be found in Row 1 of Table 2.

Translating those to a formula:

C6: =INDEX(Table 2::A:Z,MATCH($B6,Table 2:$B,0),MATCH(C$1,Table 2::$1:$1,-1)

A:Z assumes the last column of Table 2 is column Z.

The 0 in the first MATCH is 'Find value.'

The -1 in the second MATCH is 'Find Smallest'.

I've not yet tested this, and won't have an opportunity to do so for a few hours.

Try it in C6, and report any mishaps. Will expand to apply to other cells later today.

Regards,

Barry

OK.

Some questions:

What is the actual date and time value behind this 'date'? What does 'These dates…will never change" mean?

What values do you "want to be able to edit", "and have the table above change also"?

I'm not certain I'm on the right track in my previous post. I'll wai for more details regarding these quetions and smilar ons regarding the actual content (as opposed to 'displayed' content' of the cells in the tables.

Regards,

Barry