Excel 2013 index, match & lookup function working, would like add IF function.
Hello Experts,
I have an index, match & lookup function that is working:
=INDEX($W$2:$W$17,MATCH(I2
Column W are prices, based on a length equal to or less than 1 inch.
Column V are inches in decimal, from 0 to 1 Inch (16 rows) Example 0, .12500, .18750, .25000 etc.
represents diameters.
Cell I2 is the data for various diameters in the spreadsheet, (in decimal form)
Based on the diameters it will return the associated price, (this is working).
There are 4 other columns that have different prices based 2, 3, 4, and 5 inches,
columns AA, AE, AI, & AM. Column V is used for the diameters, it's constant.
What I have done is manually change the formula to the appropriate column based on the length for the correct price.
Is there a way to add an IF function wrapped around the current formula I have to incorporate the various lengths?
For example, column W is for lengths less than or equal to 1 inch.
column AA is for over 1 inch and < = 2 inches.
column AE is for over 2 inches and < = 3 inches.
column AI is for over 3 inches and < = 4 inches.
column AM is for over 4 inches and < = 5 inches.
The data in the spreadsheet has over 2 thousand items that is used to find the price based on the diameter and length.
Any ideas/examples will be greatly appreciated!
Thanks forwiw.
I have an index, match & lookup function that is working:
=INDEX($W$2:$W$17,MATCH(I2
Column W are prices, based on a length equal to or less than 1 inch.
Column V are inches in decimal, from 0 to 1 Inch (16 rows) Example 0, .12500, .18750, .25000 etc.
represents diameters.
Cell I2 is the data for various diameters in the spreadsheet, (in decimal form)
Based on the diameters it will return the associated price, (this is working).
There are 4 other columns that have different prices based 2, 3, 4, and 5 inches,
columns AA, AE, AI, & AM. Column V is used for the diameters, it's constant.
What I have done is manually change the formula to the appropriate column based on the length for the correct price.
Is there a way to add an IF function wrapped around the current formula I have to incorporate the various lengths?
For example, column W is for lengths less than or equal to 1 inch.
column AA is for over 1 inch and < = 2 inches.
column AE is for over 2 inches and < = 3 inches.
column AI is for over 3 inches and < = 4 inches.
column AM is for over 4 inches and < = 5 inches.
The data in the spreadsheet has over 2 thousand items that is used to find the price based on the diameter and length.
Any ideas/examples will be greatly appreciated!
Thanks forwiw.
Here is the formula. hope this produce the expected result.
=IF(V2<=1,INDEX($W$2:$W$17
it will be good if you send an example file so that we test the formula.
=IF(V2<=1,INDEX($W$2:$W$17
it will be good if you send an example file so that we test the formula.
Hi,
pls try ( since the prices are every 4 columns)
pls try ( since the prices are every 4 columns)
=INDEX(OFFSET($W$2:$W$17,0,4*(5-MATCH(I2,{5;4;3;2;1},-1))),MATCH(I2,$V$2:$V$17)+(LOOKUP(I2,$V$2:$V$17)<>I2))ASKER
Experts,
I should have mentioned that the data in the spreadsheet for lengths is in column K.
Based on the cell value (length) in this column, would determine which Index ( W, AA, AE, AI & AM) would be used.
Column I represents the data for the diameters.
I've included some test data in attached spreadsheet.
Thanks.
test.xlsx
I should have mentioned that the data in the spreadsheet for lengths is in column K.
Based on the cell value (length) in this column, would determine which Index ( W, AA, AE, AI & AM) would be used.
Column I represents the data for the diameters.
I've included some test data in attached spreadsheet.
Thanks.
test.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Worked like a Charm! Excellent solution! Thank You!
Could you attach an example file.
Best,