Spreadsheet: 845998d1697295646-xlookup-from-table-where-the-looked-up-columns-may-move-example-spreadsheet-fixed
Sheet: Sheet1

Status | Customer | Person ID | Age | Sex | Country | Weight (kg) | Units | Last Shipment | Decision Date |  |  |  |  | Status | Customer | Country | Person ID | Age | Sex | Weight (kg) | Decision Date | Units | Last Shipment
Open | Customer A | AAA1 | 64 | Male | United Kingdom | 82 | 345 | 2023-03-21 00:00:00 | 2022-08-30 00:00:00 |  |  |  |  | Open | Customer A | United Kingdom | AAA1 | 64 | Male | 82 | 2022-08-30 00:00:00 | 345 | 2023-03-21 00:00:00
Open | Customer A | AAA2 | 65 | Female | United Kingdom | 100 | 43 | 2023-06-22 00:00:00 | 2022-09-14 00:00:00 |  | Person ID | Decision Date |  | Open | Customer A | United Kingdom | AAA2 | 65 | Female | 100 | 2022-09-14 00:00:00 | 43 | 2023-06-22 00:00:00
Open | Customer B | AAA3 | 53 | Female | United Kingdom | 63 | 23 | 2023-06-20 00:00:00 | 2022-10-12 00:00:00 |  | AAA4 |  |  | Open | Customer B | United Kingdom | AAA3 | 53 | Female | 63 | 2022-10-12 00:00:00 | 23 | 2023-06-20 00:00:00
Open | Customer B | AAA4 | 43 | Female | United Kingdom | 81 | 12 | 2023-07-24 00:00:00 | 2022-10-12 00:00:00 |  |  |  |  | Open | Customer B | United Kingdom | AAA4 | 43 | Female | 81 | 2022-10-12 00:00:00 | 12 | 2023-07-24 00:00:00
Open | Customer C | AAA5 | 76 | Female | United Kingdom | 102 | 43 | 2023-07-24 00:00:00 | 2022-10-25 00:00:00 |  |  |  |  | Open | Customer C | United Kingdom | AAA5 | 76 | Female | 102 | 2022-10-25 00:00:00 | 43 | 2023-07-24 00:00:00
Open | Customer C | AAA6 | 45 | Female | United Kingdom | 62 | 123 | 2023-07-12 00:00:00 | 2022-11-04 00:00:00 |  |  |  |  | Open | Customer C | United Kingdom | AAA6 | 45 | Female | 62 | 2022-11-04 00:00:00 | 123 | 2023-07-12 00:00:00
Open | Customer C | AAA7 | 43 | Male | United Kingdom | 86 | 12 | 2023-07-10 00:00:00 | 2022-11-04 00:00:00 |  |  |  |  | Open | Customer C | United Kingdom | AAA7 | 43 | Male | 86 | 2022-11-04 00:00:00 | 12 | 2023-07-10 00:00:00
The above table is a simplifed version of the actual table which has many more row and columns.
1. I want to lookup Person from cell [NAME] (currently person AAA4)
2. I want to bring back the Decision Date for that person in cell M4
3. This works if the columns do not move BUT….
4. Each week, data is copied from another table and pasted as values into this table. The two tables cannnot be linked with formulas due to access rights and data sensitivity.
5. Sometimes, the position of the columns may be altered in the table we copy data from. Due to the number of columns it is difficult to notice this before copying across.
6. I need the formula in cell M4 to capture the Decision Date for the Lookup Person regardless of where the columns of Person ID and Decision Date are in the table.
7. As an example, if the data from the table in columns O to X was copied as pasted as values into the table in columns A to J, the formula in cell M4 should still bring back the date of 12th October '22.
Any help much appreciated!
Status | Customer | Country | Person ID | Age | Sex | Weight (kg) | Decision Date | Units | Last Shipment
Open | Customer A | United Kingdom | AAA1 | 64 | Male | 82 | 2022-08-30 00:00:00 | 345 | 2023-03-21 00:00:00
Open | Customer A | United Kingdom | AAA2 | 65 | Female | 100 | 2022-09-14 00:00:00 | 43 | 2023-06-22 00:00:00
Open | Customer B | United Kingdom | AAA3 | 53 | Female | 63 | 2022-10-12 00:00:00 | 23 | 2023-06-20 00:00:00
Open | Customer B | United Kingdom | AAA4 | 43 | Female | 81 | 2022-10-12 00:00:00 | 12 | 2023-07-24 00:00:00
Open | Customer C | United Kingdom | AAA5 | 76 | Female | 102 | 2022-10-25 00:00:00 | 43 | 2023-07-24 00:00:00
Open | Customer C | United Kingdom | AAA6 | 45 | Female | 62 | 2022-11-04 00:00:00 | 123 | 2023-07-12 00:00:00
Open | Customer C | United Kingdom | AAA7 | 43 | Male | 86 | 2022-11-04 00:00:00 | 12 | 2023-07-10 00:00:00
Status | Customer | Person ID | Age | Sex | Country | Weight (kg) | Units | Last Shipment | Decision Date
Open | Customer A | AAA1 | 64 | Male | United Kingdom | 82 | 345 | 2023-03-21 00:00:00 | 2022-08-30 00:00:00
Open | Customer A | AAA2 | 65 | Female | United Kingdom | 100 | 43 | 2023-06-22 00:00:00 | 2022-09-14 00:00:00
Open | Customer B | AAA3 | 53 | Female | United Kingdom | 63 | 23 | 2023-06-20 00:00:00 | 2022-10-12 00:00:00
Open | Customer B | AAA4 | 43 | Female | United Kingdom | 81 | 12 | 2023-07-24 00:00:00 | 2022-10-12 00:00:00
Open | Customer C | AAA5 | 76 | Female | United Kingdom | 102 | 43 | 2023-07-24 00:00:00 | 2022-10-25 00:00:00
Open | Customer C | AAA6 | 45 | Female | United Kingdom | 62 | 123 | 2023-07-12 00:00:00 | 2022-11-04 00:00:00
Open | Customer C | AAA7 | 43 | Male | United Kingdom | 86 | 12 | 2023-07-10 00:00:00 | 2022-11-04 00:00:00