Formula copies down & across using table with two vertical conditions & one horizontal

Hello

Worked example attached.

I have updated the explanation in the worked example so there is no history of the previous explanation.

Ability to copy formula down & across without having to manually change the formula to skip yellow rows (revision dates)
Take the characteritics in the table according to the variable PO in column D, variable date in row 1 and insert the Insert the characteristic (eg ADV) from the table in the relevant variable date (row1) and PO number (column D). The column date range in the table which is matched to the date variable in row 1, always takes the most recent date range according to the revision date to allow a history of the date changes can be retained. The Revision Date is simply a version control and are not matched against the variable dates in row 1.

David

Can you update your sample sheet? Remove all references to other formulas and all "examples" of data not relavant. List one excellent table of source data in the correct format, and manually mockup up complete set of results above in your table.

See if this workbook does as you need

It uses Dynamic Named ranges

Name:= "PurchaseOrder"
Refers to

Formula:

Please Login or Register  to view this content.

Name:= "OrderData"
Refers to

Formula:

Please Login or Register  to view this content.

Then in E2, Drag Across then Down.

Formula:

Please Login or Register  to view this content.

In E2, copied down and across, my horrible monster formula...

=IFERROR(INDEX(OFFSET($C$19, MATCH($D2,$C$20:$C$59,0), 1, 7,), MATCH(E$1, OFFSET($C$19, MATCH($D2, $C$20:$C$59, 0), MATCH(MAX(OFFSET($C$19, MATCH($D2, $C$20:$C$59,0)-1, , , 10)), OFFSET($C$19,MATCH($D2, $C$20:$C$59,0)-1, , , 10), 0)-1, 7, ), 0)), "")



EDIT: Hat-tip to Marcol, I like that!

@ JB, thanks for the hat-tip.

The OPs' profile indicates 2003.

IFERROR() is not available in 2003 and the nested IF(), ISERROR() becomes to long, or complicated for 2003 to accept, hence the use of named ranges.

The OP posted an xlsx file, so one presumes his profile is inaccurate.