Absolute values and locked cells

[dmotz]

I am trying to drag a formula across many columns, while maintaining the first two cells. However, I am having a problem locking the first two cells because once I select the range, it automatically adds the brackets for a Table, and I don’t know how to add a locked $ to that ($C$2:$C$100 instead of Table2[Name]).

Ultimately, I’m trying to drag the bottom right corner of the cell across multiple columns and only change the 3rd item, the Return Array in a Lookup formula.



Example:

=XLOOKUP([@Name], Table2[Name], Table2[Address])

=XLOOKUP([@Name], Table2[Name], Table2[City])

=XLOOKUP([@Name], Table2[Name], Table2[State])

Etc.



Is there a way to do this? How do I lock a table range when the brackets are added?



Thank you!

 

[alansidman]

Ultimately, I’m trying to drag the bottom right corner of the cell across multiple columns and only change the 3rd item, the Return Array in a Lookup formula.

What happens when you attempt this step? Do you get the expected results?

 

[Peter_SSs]

Welcome to the MrExcel board!

Assuming that your formula is in Table1, try this

=XLOOKUP(@Table1[[Name]:[Name]], Table2[[Name]:[Name]], Table2[Address])

However, it may be simpler to replace that first part with a normal 'locked column address'. For example if your first name in Table1 was in, say, M2 then

=XLOOKUP($M2, Table2[[Name]:[Name]], Table2[Address])

 

[dmotz]

What happens when you attempt this step? Do you get the expected results?

What happens when you attempt this step? Do you get the expected results?

Thank you for the response. When I drag from the bottom right corner, all the cells change to the cell that I'm dragging them to (because they aren't locked). Example: =XLOOKUP([@Name], Table2[Name], Table2[Address]) becomes =XLOOKUP([@Address], Table2[Address], Table2[Phone]) then =XLOOKUP([@Phone], Table2[Phone], Table2[City]) then =XLOOKUP([@City], Table2[City], Table2[State]) etc. Nothing is locked. I guess the question I have is how do you lock a cell from changing when you drag it when the range is listed in brackets instead of A2:A100 format?

 

[dmotz]

Welcome to the MrExcel board!

Assuming that your formula is in Table1, try this

=XLOOKUP(@Table1[[Name]:[Name]], Table2[[Name]:[Name]], Table2[Address])

However, it may be simpler to replace that first part with a normal 'locked column address'. For example if your first name in Table1 was in, say, M2 then

=XLOOKUP($M2, Table2[[Name]:[Name]], Table2[Address])

That didn't work for me but thank you for trying. Maybe the ultimate question I have is how do you lock a cell from changing when you drag it when the range is listed in brackets [@[Name]] instead of A2:A100 format?

 

[Peter_SSs]

That didn't work for me

In what way did it not work? Also, does it mean you tried both suggestions or just one of them?
Perhaps you can post a small set of sample data with so we can test with your data/layout. Here is mine using both suggestions. In Tables 1 and 3 I entered the first formula in the top cell of 'Column1' and those formulas automatically filled down the tables. I then selected all the formulas in 'Column1' and dragged right.

dmotz.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Table2 ->	Name	Address	City	State	abc		Table1 ->	Name	Column1	Column2	Column3	Column4
2		Name1	Addr1	City1	State1	abc1			Name3	Addr3	City3	State3	abc3
3		Name2	Addr2	City2	State2	abc2			Name4	Addr4	City4	State4	abc4
4		Name3	Addr3	City3	State3	abc3			Name2	Addr2	City2	State2	abc2
5		Name4	Addr4	City4	State4	abc4
6		Name5	Addr5	City5	State5	abc5
7		Name6	Addr6	City6	State6	abc6		Table3 ->	Name	Column1	Column2	Column3	Column4
8		Name7	Addr7	City7	State7	abc7			Name3	Addr3	City3	State3	abc3
9									Name4	Addr4	City4	State4	abc4
10									Name2	Addr2	City2	State2	abc2
11
Sheet1

Cell Formulas
Range	Formula
K2:K4	K2	=XLOOKUP(@Table1[[Name]:[Name]], Table2[[Name]:[Name]], Table2[Address])
L2:L4	L2	=XLOOKUP(@Table1[[Name]:[Name]], Table2[[Name]:[Name]], Table2[City])
M2:M4	M2	=XLOOKUP(@Table1[[Name]:[Name]], Table2[[Name]:[Name]], Table2[State])
N2:N4	N2	=XLOOKUP(@Table1[[Name]:[Name]], Table2[[Name]:[Name]], Table2[abc])
K8:K10	K8	=XLOOKUP($J8, Table2[[Name]:[Name]], Table2[Address])
L8:L10	L8	=XLOOKUP($J8, Table2[[Name]:[Name]], Table2[City])
M8:M10	M8	=XLOOKUP($J8, Table2[[Name]:[Name]], Table2[State])
N8:N10	N8	=XLOOKUP($J8, Table2[[Name]:[Name]], Table2[abc])