Index/Match Cannot The Find Column

[williamfr]

Good evening everyone. I'm having a really tough time and don't know what else to do.

I'm creating a quoting tool for a sales team. They choose the Product, Description(sub product), and contract length in the header to spit out the correct price for the product. It finds the correct row number when i go through the error breakdown, but it cannot find the correct Term price spread across 5 columns. The first block is my form. Data validations drop down lists are bolded. Second block is my data. No matter how i enter the formula into the term column on the form, i either get an error or it returns the base price. It never returns the correct term price no matter what i've tried. the third block is my current formula. For reference, the correct answer on my Managed WiFi Access point should be $10.46, not $14.95.

I understand i'm doing something wrong here, because the direction of data changes with the column array. Unfortunately my brain can't handle it. I've been googling examples and tutorials for like 8 hours. Does anyone have any ideas?

D​

E​

F​

G​

H​

I​

Product	Description	Quantity	3 YR Term	Total	Bill Period
Installation Fee	Phone/Internet/TV	1	$ 100.00	$ 100.00	One Time Fee
Managed WiFi	Access Point	2	$ 14.95	$ 29.90	Monthly

Table Name: Data

A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
Region	Technology	Network	Product	Description	Billing	Base Price	1 YR Term	2 YR Term	3 YR Term	5 YR Term
All	All	All	Managed WiFi	Access Point	Monthly	$ 14.95	-	$ 11.96	$ 10.46	$ 10.46
All	All	All	Installation Fee	Phone/Internet/TV	One Time Fee	$ 100.00	$ 100.00	$ 100.00	$ 100.00	$ 100.00

=INDEX(Data[[Product]:[5 YR Term]],MATCH([@Product]&[@Description],Data[Product]&Data[Description],0),MATCH(Quote[[#Headers],[3 YR Term]],Data[[#Headers],[Base Price]:[5 YR Term]],0))

 

[SunnyAlv]

Like This ?

Xlookup Version :

Index Match Version :

 

[williamfr]

Like This ?

Xlookup Version :
View attachment 98761


Index Match Version :

View attachment 98762

the term column, G1, is a data validation drop down list that can be changed from Base Price, 1 YR Term, 2 YR Term, 3 YR Term, 5 YR Term. If the user chose 2 year term, then it would change the access point price from 10.46 to 11.96. Thats the part i'm struggling with..having it find the correct price across those 5 term columns, based on the term chosen :/ Base Price is included even though it doesn't say term.

 

[SunnyAlv]

the term column, G1, is a data validation drop down list that can be changed from Base Price, 1 YR Term, 2 YR Term, 3 YR Term, 5 YR Term. If the user chose 2 year term, then it would change the access point price from 10.46 to 11.96. Thats the part i'm struggling with..having it find the correct price across those 5 term columns, based on the term chosen :/ Base Price is included even though it doesn't say term.

Ah, I see, so the trigger is based on which year is selected, the price of the selected year will appear, what do you mean by that?

 

[williamfr]

Product	Description	Quantity	3 YR Term This is a drop down list cell	Total	Bill Period
Managed WiFi This is a drop down list cell	Access Point This is a drop down list cell	1	$ 14.95	$ 14.95	Monthly

If 3 YR Term is chosen above, it should show the corresponding 3 year price for a managed wifi access point, which would be $10.46. If the user chooses 2 year, it would show $11.96 like below.

Product	Description	Quantity	2 YR Term This is a drop down list cell	Total	Bill Period
Managed WiFi This is a drop down list cell	Access Point This is a drop down list cell	1	$ 11.96	$ 11.96	Monthly

My brain is fried so i hope i'm making sense

 

[williamfr]

If 3 YR Term is chosen above, it should show the corresponding 3 year price for a managed wifi access point, which would be $10.46. If the user chooses 2 year, it would show $11.96 like below.

My brain is fried so i hope i'm making sense

the top is returning a "base price" instead of a 3 year price in my example

 

[SunnyAlv]

try this and trigger on G1

=XLOOKUP(D2&E2&I2,$D$7:$D$8&$E$7:$E$8&$F$7:$F$8,TRANSPOSE(HLOOKUP($G$1,$G$6:$K$8,{2,3},0)))

 

[williamfr]

try this and trigger on G1




View attachment 98763

that works for the small scale test! however...after setting it up with my actual product/data page references..it doesn't seem to work. currently i have rows 3-61 full of data and plan to have likely upwards to 500 rows. is that {2,3} limiting me to just the first two rows?

 

[SunnyAlv]

honestly, its really hard but i have 2 option for u ya

First u follow my formula below, and u can add some criteria before IF Formula and adding additional IF Formula

=LET(
BasePrice,XLOOKUP(CONCATENATE(A11,B11,F11),CONCATENATE($D$2:$D$5,$E$2:$E$5,$F$2:$F$5),$G$2:$G$5),

OneYear,XLOOKUP(CONCATENATE(A11,B11,F11),CONCATENATE($D$2:$D$5,$E$2:$E$5,$F$2:$F$5),$H$2:$H$5),

TwoYear,XLOOKUP(CONCATENATE(A11,B11,F11),CONCATENATE($D$2:$D$5,$E$2:$E$5,$F$2:$F$5),$I$2:$I$5),

ThreeYear,XLOOKUP(CONCATENATE(A11,B11,F11),CONCATENATE($D$2:$D$5,$E$2:$E$5,$F$2:$F$5),$J$2:$J$5),

FiveYear,XLOOKUP(CONCATENATE(A11,B11,F11),CONCATENATE($D$2:$D$5,$E$2:$E$5,$F$2:$F$5),$K$2:$K$5),

IF($D$10=$G$1,BasePrice,IF($D$10=$H$1,OneYear,IF($D$10=$I$1,TwoYear,IF($D$10=$J$1,ThreeYear,FiveYear)))))

Second, u must add 1 Column for Trigger only, so u easy to lookup the data

If I'm wrong please forgive
Thanks

 

[Peter_SSs]

Welcome to the MrExcel board!

Does this do what you want?

williamfr.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1				Product	Description	Quantity	3 YR Term	Total	Bill Period
2				Installation Fee	Phone/Internet/TV	1	$100.00	$100.00	One Time Fee
3				Managed WiFi	Access Point	2	$10.46	$20.92	Monthly
4
5
6
7
8	Region	Technology	Network	Product	Description	Billing	Base Price	1 YR Term	2 YR Term	3 YR Term	5 YR Term
9	All	All	All	Managed WiFi	Access Point	Monthly	$14.95	-	$11.96	$10.46	$10.46
10	All	All	All	Installation Fee	Phone/Internet/TV	One Time Fee	$100.00	$100.00	$100.00	$100.00	$100.00
11
12
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=FILTER(INDEX(Data,0,MATCH(Quote[[#Headers],[3 YR Term]],Data[#Headers],0)),(Data[Product]=[@Product])*(Data[Description]=[@Description]),"")
H2:H3	H2	=[@Quantity]*[@[3 YR Term]]

Cells with Data Validation
Cell	Allow	Criteria
G1	List	=$G$8:$K$8