Lookup functions based on array position

Hi,

My problem relates to looking up a value based on the position of the column (one column in a table of multiple columns) that I'm restricting my lookup to.

The broader data I'm using is as follows: I am building a table of different financial securities that are divided by domicile (states in Australia). I need to adjust the cash flow days for public holidays which I have separated based on state and national public holidays.
Next to the name of each security I have inserted a drop down list from which the state domicile of each security can be selected. After this I have inserted a function that calculates the next cash flow day for each security. Based on the domicile selection of each security I want to be able to look up the calculated cash flow day in the table of sorted public holidays by first narrowing down my matching to the specific column of public holidays based on domicile so that I only get a match if the cash flow day of a security falls on a date that is a public holiday for the same domicile in which that security is issued.

I'm open to any alternative ways of doing this given that public holidays are always sorted by domicile and not collated into the one column without domicile based sorting.

Thanks.

In my data set I don't have the equivalent of column A in your table. My data is only sorted into different columns by state and not by rows

Worksheet 1: Security database

Security Cash flow date W/end test Pass/Fail Weekend adjustment Security Domicile Public Holiday Test Pass/Fail Public Holiday Adjustment Public Holiday Test Pass/Fail Final Date
Security 1 Date 1 Test 1 Fail Second calculation State 1 Test 1a Fail Third calculation Test 1b Pass Date 1 adjusted for tests 1 & 1a
Security 2 Date 2 Test 2 Pass State 2 Test 2a Pass Date 2
Security 3 Date 3 Test 3 Fail Second calculation State 3 Test 3a Fail Third calculation Test 3b Pass Date 3 adjusted for tests 3 & 3a
Security 4 Date 4 Test 4 Pass State 4 Test 4a Pass Date 4


Worksheet 2: Public Holidays List

State 1 State 2 State 3 State 4
Date 1 Date 1 Date 1 Date 1
Date 2 Date 2 Date 2 Date 2
Date 3 Date 3 Date 3 Date 3
Date 4 Date 4 Date 4 Date 4
Date 5 Date 5 Date 5 Date 5

Sample.docx

Please find sample attached

Sorry everyone I'll try uploading something now

Apologies for the inconvenience in not putting up spreadsheets last night. Also apologies for the late reply but I managed to get it to work with a few nested if/index/match functions (I'm happy to share for anyone that's interested - it may even give someone the opportunity to refine it and make it more function efficient- just let me know )

Thanks to everyone that tried to help (given I didn't articulate my problem that clearly) it is greatly appreciated.

FJMoussally signing off