nested if statement with index and match within

**darkowen** · 01-22-2012, 10:28 PM

Hi All,

I am preparing an excel spread sheet for work and it requires that a sales consultant select a particular product from six choices and enter in a desired size in width and length. From this data i need to create a formula that will calculate the material cost of that particular product.

I have 6 tables in one sheet that will be hidden and so i wanted to know if anyone could help me create a formula that would work something like

=if(cell="category 1",index(match),if(cell="category 2",index(match),if(cell=category 3",index(match) ....... and so on

where index(match) is my formula to work out which price is returned

any help is greatly appreciated.

regards,

matt

**vlady** · 01-22-2012, 10:34 PM

Hello good morning.
A dummy workbook would help us to study your issues.

Cheers,
vlady

**darkowen** · 01-23-2012, 09:32 PM

Vanguard Template.xlsx

Attached is the excel sheet that i have been refering to

It is not complete and will require me to insert more categories into sheet4 and more kits into sheet3

Cheers,

Owen

**darkowen** · 02-01-2012, 01:20 AM

anyone got some input on this problem?

im really stuck,

cheers.

Matt

**vlady** · 02-01-2012, 02:02 AM

Hello

something like??
Maybe......

=IF(H4="Category 1", INDEX(Q5:T10,MATCH(G9,Q6:Q10,0),2),IF(H4="Category 2",INDEX(Q5:T10,MATCH(G9,Q6:Q10,0),3),INDEX(Q5:T10,MATCH(G9,Q6:Q10,0),4)))

Vanguard Template.xlsx

But there is always a better solution out there.