Help with converting xlookup equation into a custom function with one input.

[rorymac]

Hi,

I did use match, index and lookup but now use the xlookup to collate relevant data from different tables for information when adding new data to the new new table. Ie copy contact information to help identify the client to add more data against that client.

I have generated an equation that does this, see below, but even though xlookup simplifies the equation, putting in new tales, copying the equation to another sheet I still manage to misspell something, or it take a while to fully understand the equation again to get it working again. I have tried to write a custom function with one variable input but failed.

I was usign the xlookup as
lookup_value - This will always be the first row of the new table and entered manually, townlink reference, and will be a unique name for one property across all tables. .
lookup_array - The first row, townlink reference of the table that data being extracted from.
return_array - The row in the table with the data beign extracted.

To reduce my manual input and thus less likely to get errors I used variables in rows 2 and 3 that I can change rather than change the actual equation. I hide rows 2 & 3 after table setup.
A3 - is the name of the new table, so when generate another new table I can change this, and it will correct the equation
B2 - is the name of the table the data is being extracted from; each column can be extracting from different tables.
B3 - is equal B5 and the variable to be extracted. I could not get the equation working by referencing the table heading directly.

I have tried to write a custom equation, as it should only require one variable, the name of the table the data is being extracted from. Thats if the other inputs above can be obtained automatically from the cell location?

The formular without referencing the cells above is below for reference.
=XLOOKUP(tbl_Attic[@[TownLink referance]:[TownLink referance]],tbl_Contacts[[#All],[TownLink referance]:[TownLink referance]],tbl_Contacts[[#All],[SEAI reference]],"error")

The mini sheet for both tabs below. Any help will be a greatly received.

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		tbl_Contacts	tbl_Contacts	tbl_Contacts	tbl_Contacts	Table3
3	tbl_Townlink_WO	SEAI reference	SEAI database link	Surname	TL Addr	PM
4							Atic
5	TownLink referance	SEAI reference	SEAI database link	Surname	TL Addr	PM	Attic 100mm	Attic 100mm2	Attic 100mm3	Attic 100mm4	Attic 100mm5	IWI: Sloped	LL Roof vent
6	D190	WH571	##	Ske	264	Rory	60			43	1		109800
7	D191	WH576	##	Sta	4 GL	Dara
8	D192	WH591	##	O C	54 O	Dara	45
9	D193	WH631	##	Wal	62 S	Derek			46		1
10	D194	WH634	##	Mar	81 T	Dara	40				1
Townlink_WO

Cell Formulas
Range	Formula
B3	B3	=tbl_Townlink_WO[[#Headers],[SEAI reference]]
C3	C3	=tbl_Townlink_WO[[#Headers],[SEAI database link]]
D3	D3	=tbl_Townlink_WO[[#Headers],[Surname]]
E3	E3	=tbl_Townlink_WO[[#Headers],[TL Addr]]
B6:F10	B6	=XLOOKUP(INDIRECT($A$3&"[@[TownLink referance]:[TownLink referance]]"),INDIRECT(B$2&"[[#All],[TownLink referance]:[TownLink referance]]"),INDIRECT(B$2&"[[#All],["&B$3&"]]"),"error")

Book1.xlsm
	A	B	C	D	E	F	G	H
1
2
3	TownLink referance	SEAI reference	Title	First	Surname	TL addr	Eircode	TL Primary Phone Number
4	D190	WH571	Mr	264	Ske	264
5	D191	WH576	Mr	4 GL	Sta	4 GL
6	D192	WH591	Mrs	54 O	O C	54 O
7	D193	WH631	Ms	62 S	Wal	62 S
8	D194	WH634	Ms	81 T	Mar	81 T
9
10
11
12	TownLink referance	PM
13	D190	Rory
14	D191	Dara
15	D192	Dara
16	D193	Derek
17	D194	Dara
Contacts

Cell Formulas
Range	Formula
D4:D8	D4	=LEFT([@[TL addr]],4)