Is there a way to drag the formula down but changing the column

[ranjith2523]

Hello Friends,

I have data from Column A to Column XX and wrote an XLOOKUP formula to fetch the data from another sheet.

For the first row, the formula works as expected but when I copy the formula and apply it to the second row (For Product 847309-B21) the procedure automatically changes the cell value. The concat range change from K1:K3 to K2 to K4 hence not fetching the price from Sheet3. I have the data for 100+ columns hence manually changing the formula in each column will take a very long time. Please have a look at the below data and help me with this.

I believe we can do this by joining the Transpose Filter and Index formula, I have tried my best but have not been able to do that hence seeking your help.

Pricing Automation_V11.xlsm
	A	B									K	L	M	N	O
1	FEP-PRICES	GEOGRAPHY									US	US	00	IT	ES
2		CURRENCY									USD	USD	USD	EUR	EUR
3		INCOTERM									RP	DDP	DDP	DDP	DDP
4		PRICE LIST
10	SKU	Option										US LP	GLP
11	707853-B22										361.00	361.00		429.00	429.00
12	847309-B21
Price Import

Cell Formulas
Range	Formula
K11:O12	K11	=XLOOKUP(CONCAT($A11,K1:K3),Sheet3!$K:$K,Sheet3!$G:$G,"",0,1)

SHEET3 DATA

Material	Variant	Ma	Curr	PL	S	Amount	Valid From	Valid To	CON	PPCON
707853-B22		IT	EUR	DDP		429.00	01-Nov-22	31-Dec-99	ITEURDDP	707853-B22ITEURDDP
707853-B22		US	USD	DDP		361.00	06-Jun-15	31-Dec-99	USUSDDDP	707853-B22USUSDDDP
707853-B22		ES	EUR	DDP		429.00	01-Nov-22	31-Dec-99	ESEURDDP	707853-B22ESEURDDP
707853-B22		US	USD	RP		361.00	06-Jun-15	31-Dec-99	USUSDRP	707853-B22USUSDRP
847309-B21		ES	EUR	DDP		494.00	01-Nov-22	31-Dec-99	ESEURDDP	847309-B21ESEURDDP
847309-B21		IT	EUR	DDP		494.00	01-Nov-22	31-Dec-99	ITEURDDP	847309-B21ITEURDDP
847309-B21		US	USD	RP		424.00	17-Nov-15	31-Dec-99	USUSDRP	847309-B21USUSDRP

 

[pjmorris]

use K$1:K$3

A '$' sign in from of any part of a cell reference makes that reference 'absolute' and it won't be changed when a formula is copied.

HTH

 

[ranjith2523]

use K$1:K$3

A '$' sign in from of any part of a cell reference makes that reference 'absolute' and it won't be changed when a formula is copied.

HTH

Simple but this fulfill my requirement. Thanks a lot Morris