ranjith2523
Board Regular
- Joined
- Apr 29, 2014
- Messages
- 137
- Office Version
- 365
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.
SHEET3 DATA
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 | ||