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



## ranjith2523 (Jan 4, 2023)

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.xlsmABKLMNO1FEP-PRICESGEOGRAPHYUSUS00ITES2CURRENCYUSDUSDUSDEUREUR3INCOTERMRPDDPDDPDDPDDP4PRICE LIST10SKUOptionUS LPGLP11707853-B22361.00361.00 429.00429.0012847309-B21     Price ImportCell FormulasRangeFormulaK11:O12K11=XLOOKUP(CONCAT($A11,K1:K3),Sheet3!$K:$K,Sheet3!$G:$G,"",0,1)

SHEET3 DATA

MaterialVariantMaCurrPLSAmountValid FromValid ToCONPPCON707853-B22ITEURDDP429.0001-Nov-2231-Dec-99ITEURDDP707853-B22ITEURDDP707853-B22USUSDDDP361.0006-Jun-1531-Dec-99USUSDDDP707853-B22USUSDDDP707853-B22ESEURDDP429.0001-Nov-2231-Dec-99ESEURDDP707853-B22ESEURDDP707853-B22USUSDRP361.0006-Jun-1531-Dec-99USUSDRP707853-B22USUSDRP847309-B21ESEURDDP494.0001-Nov-2231-Dec-99ESEURDDP847309-B21ESEURDDP847309-B21ITEURDDP494.0001-Nov-2231-Dec-99ITEURDDP847309-B21ITEURDDP847309-B21USUSDRP424.0017-Nov-1531-Dec-99USUSDRP847309-B21USUSDRP


----------



## pjmorris (Jan 4, 2023)

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 (Jan 5, 2023)

pjmorris said:


> 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


----------

