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))

 

[williamfr]

Thank you to @SunnyAlv for working with me on this. I know you put in some work on it!

@Peter_SSs Yes sir that worked!! I can't even begin to tell you how thrilled i am. I wish there was some way i could thank ya'll for the assistance.

I think it's safe to say this was well beyond my knowledge. Do you mind breaking down *how* the entire formula works? i'd like to learn from it. I didn't quite understand what the purpose of the filter function was at the beginning. Also i see your multiplied the product and the description, or is that not an actual multiplier function in this case? it's easier for me to understand formulas if its "said out loud" or written out if that makes sense.

At any rate..man..load of my back lol.

 

[Peter_SSs]

Do you mind breaking down *how* the entire formula works?

=FILTER(INDEX(Data,0,MATCH(Quote[[#Headers],[3 YR Term]],Data[#Headers],0)),(Data[Product]=[@Product])*(Data[Description]=[@Description]),"")

The red part extracts the the column from the bottom table that has the same heading as the row 1 drop-down in the top table.
The blue part then filters that column to only show the rows (but I'm assuming only one row) where product and description match the row in the top table.

Also i see your multiplied the product and the description, or is that not an actual multiplier function in this case?

Multiplying conditions like that is the same as saying first condition AND second condition. So filter rows where product is correct AND description is correct.