Cross Reference Two Tables, and Table Headers, Return Value

[MrCameronExcel]

Hi,

I have two tables, and I want to cross reference the start dates per window area (large lounge, small lounge, bedroom 1 etc.) from TABLE 1 into TABLE 2.

I have previously used the FILTER formula to return values from a column based on a cross reference of columns across tables, but in this scenario, I would need to cross reference the columns level and flat across the tables, but also cross reference Window column to the column headers (or first row) in Table 1.

How can I achieve this? Is there a formula to cross reference column to row?

TABLE 1

Level	Flat	Number of Windows	Large Lounge	Small Lounge	Bedroom 1	Bedroom 2	Bedroom 3	Kitchen	Bathroom	WC
16​	119​	6​	16/01/2023​	16/01/2023​	17/01/2023​	17/01/2023​	18/01/2023​	18/01/2023​
12​	89​	5​	16/01/2023​	16/01/2023​	17/01/2023​			17/01/2023​	18/01/2023​
12​	87​	3​	19/01/2023​					19/01/2023​	20/01/2023​
12​	86​	7​	18/01/2023​	19/01/2023​	19/01/2023​	20/01/2023​		20/01/2023​	21/01/2023​	21/01/2023​
11​	82​	5​	20/01/2023​	21/01/2023​	21/01/2023​			22/01/2023​	22/01/2023​
11​	80​	3​	23/01/2023​					24/01/2023​	24/01/2023​
11​	78​	5​	24/01/2023​	25/01/2023​	25/01/2023​	26/01/2023​		26/01/2023​	27/01/2023​
9​	70​	6​	24/01/2023​	25/01/2023​	25/01/2023​	26/01/2023​	26/01/2023​	27/01/2023​
9​	68​	5​	26/01/2023​	27/01/2023​	27/01/2023​			28/01/2023​	28/01/2023​
5​	42​	6​	27/01/2023​	27/01/2023​	28/01/2023​	28/01/2023​	29/01/2023​	29/01/2023​

TABLE 2

Level	Flat	Window	Start Date
16​	119​	Large Lounge
16​	119​	Small Lounge
16​	119​	Bedroom 1
16​	119​	Bedroom 2
16​	119​	Bedroom 3
16​	119​	Kitchen
12​	89​	Large Lounge
12​	89​	Small Lounge
12​	89​	Bedroom 1
12​	89​	Kitchen
12​	89​	Bathroom
12​	87​	Large Lounge
12​	87​	Kitchen
12​	87​	Bathroom
12​	86​	Large Lounge
12​	86​	Small Lounge
12​	86​	Bedroom 1
12​	86​	Bedroom 2
12​	86​	Kitchen
12​	86​	WC
12​	86​	Bathroom
11​	82​	Large Lounge
11​	82​	Small Lounge
11​	82​	Bedroom 1
11​	82​	Kitchen
11​	82​	Bathroom
11​	80​	Large Lounge
11​	80​	Kitchen
11​	80​	Bathroom
11​	78​	Large Lounge
11​	78​	Small Lounge
11​	78​	Bedroom 1
11​	78​	Bedroom 2
11​	78​	Kitchen
11​	78​	Bathroom
9​	70​	Large Lounge
9​	70​	Small Lounge
9​	70​	Bedroom 1
9​	70​	Bedroom 2
9​	70​	Bedroom 3
9​	70​	Kitchen
9​	68​	Large Lounge
9​	68​	Small Lounge
9​	68​	Bedroom 1
9​	68​	Kitchen
9​	68​	Bathroom
5​	42​	Large Lounge
5​	42​	Small Lounge
5​	42​	Bedroom 1
5​	42​	Bedroom 2
5​	42​	Bedroom 3
5​	42​	Kitchen

 

[MrCameronExcel]

Seems an INDEX MATCH formula would work if I can work out how to MATCH multiple columns.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Level	Flat	Number of Windows	Large Lounge	Small Lounge	Bedroom 1	Bedroom 2	Bedroom 3	Kitchen	Bathroom	WC
2	16	119	6	16/01/2023	16/01/2023	17/01/2023	17/01/2023	18/01/2023	18/01/2023
3	12	89	5	16/01/2023	16/01/2023	17/01/2023			17/01/2023	18/01/2023
4	12	87	3	19/01/2023					19/01/2023	20/01/2023
5	12	86	7	18/01/2023	19/01/2023	19/01/2023	20/01/2023		20/01/2023	21/01/2023	21/01/2023
6	11	82	5	20/01/2023	21/01/2023	21/01/2023			22/01/2023	22/01/2023
7	11	80	3	23/01/2023					24/01/2023	24/01/2023
8	11	78	5	24/01/2023	25/01/2023	25/01/2023	26/01/2023		26/01/2023	27/01/2023
9	9	70	6	24/01/2023	25/01/2023	25/01/2023	26/01/2023	26/01/2023	27/01/2023
10	9	68	5	26/01/2023	27/01/2023	27/01/2023			28/01/2023	28/01/2023
11	5	42	6	27/01/2023	27/01/2023	28/01/2023	28/01/2023	29/01/2023	29/01/2023
Sheet1

Fluff.xlsm
	A	B	C	D
1	Level	Flat	Window	Start Date
2	16	119	Large Lounge	16/01/2023
3	16	119	Small Lounge	16/01/2023
4	16	119	Bedroom 1	17/01/2023
5	16	119	Bedroom 2	17/01/2023
6	16	119	Bedroom 3	18/01/2023
7	16	119	Kitchen	18/01/2023
8	12	89	Large Lounge	16/01/2023
9	12	89	Small Lounge	16/01/2023
10	12	89	Bedroom 1	17/01/2023
11	12	89	Kitchen	17/01/2023
12	12	89	Bathroom	18/01/2023
13	12	87	Large Lounge	19/01/2023
14	12	87	Kitchen	19/01/2023
15	12	87	Bathroom	20/01/2023
16	12	86	Large Lounge	18/01/2023
17	12	86	Small Lounge	19/01/2023
18	12	86	Bedroom 1	19/01/2023
19	12	86	Bedroom 2	20/01/2023
20	12	86	Kitchen	20/01/2023
21	12	86	WC	21/01/2023
22	12	86	Bathroom	21/01/2023
23	11	82	Large Lounge	20/01/2023
24	11	82	Small Lounge	21/01/2023
25	11	82	Bedroom 1	21/01/2023
26	11	82	Kitchen	22/01/2023
27	11	82	Bathroom	22/01/2023
28	11	80	Large Lounge	23/01/2023
29	11	80	Kitchen	24/01/2023
30	11	80	Bathroom	24/01/2023
31	11	78	Large Lounge	24/01/2023
32	11	78	Small Lounge	25/01/2023
33	11	78	Bedroom 1	25/01/2023
34	11	78	Bedroom 2	26/01/2023
35	11	78	Kitchen	26/01/2023
36	11	78	Bathroom	27/01/2023
37	9	70	Large Lounge	24/01/2023
38	9	70	Small Lounge	25/01/2023
39	9	70	Bedroom 1	25/01/2023
40	9	70	Bedroom 2	26/01/2023
41	9	70	Bedroom 3	26/01/2023
42	9	70	Kitchen	27/01/2023
43	9	68	Large Lounge	26/01/2023
44	9	68	Small Lounge	27/01/2023
45	9	68	Bedroom 1	27/01/2023
46	9	68	Kitchen	28/01/2023
47	9	68	Bathroom	28/01/2023
48	5	42	Large Lounge	27/01/2023
49	5	42	Small Lounge	27/01/2023
50	5	42	Bedroom 1	28/01/2023
51	5	42	Bedroom 2	28/01/2023
52	5	42	Bedroom 3	29/01/2023
53	5	42	Kitchen	29/01/2023
Sheet2

Cell Formulas
Range	Formula
D2:D53	D2	=INDEX(Sheet1!$D$2:$K$11,MATCH(A2&"|"&B2,Sheet1!$A$2:$A$11&"|"&Sheet1!$B$2:$B$11,0),MATCH(C2,Sheet1!$D$1:$K$1,0))

 

[MrCameronExcel]

Hi Fluff,

Thanks for the suggestion, I have updated my account details as you suggested.

And the formula works perfectly! Just a follow up question, what is the line " | " you used in the formula and how can I input it on my windows computer?

Thanks

 

[Fluff]

It's called a Pipe & is located on the same key as the \

 

[MrCameronExcel]

Brilliant. Thank you

 

[MrCameronExcel]

It's called a Pipe & is located on the same key as the \

Can you help me with one more thing?

Cell A5 = 18/01/2023 +0.5

I tested A5's value in I5 = A5+0.5 = 19/01/2023

However, when I use the following formula in B5, it isn't adding to 19/01/2023

B5 = WORKDAY(A5, 0.5, 0)

Is there a simple workaround formula to add 0.5 but ensure only lands on a workday? I am trying to solve it now.

Thanks

	A	B	C	D	E	F	G	H	I
1	Large Lounge	Small Lounge	Bedroom 1	Bedroom 2	Bedroom 3	Kitchen	Bathroom	WC	TEST
2	16/01/2023​	16/01/2023​	17/01/2023​	17/01/2023​	18/01/2023​	18/01/2023​
3	16/01/2023​	16/01/2023​	17/01/2023​			17/01/2023​	18/01/2023​
4	19/01/2023​					19/01/2023​	20/01/2023​
5	18/01/2023​	18/01/2023​	19/01/2023​	19/01/2023​		20/01/2023​	20/01/2023​	23/01/2023​	19/01/2023​

 

[Fluff]

As this is a totally different question, it needs a new thread. Thanks