Complex IF function

Hello,


I once again need help from excel gurus

Altought any help apriciated !


General problem :

I have to calculate price for a stop.(deliveri of "things" to companies). I have attached wkbook with sample data and samplme price list. As you see price column is empty. That is becuse I can't figure out how to do a long logical calculation if MS Excel allows only 13 nested if functions in one IF.

Sample file :

[company] - stands for name of companie (has to be included in logical calculation because priece list is only for Company1 & 2, others must be excluded)
[Sektors] - different price for different sektor. Price list for Company1 & 2 is mostly same. Only difference is "Neplanots" in 2 sektor. (I colored them)
[Rajons] - Not needed.
[Objekts] - Not needed
[Planning] -
* "Plānots" stands for planned stop
* "Neplānots" stands for unplanned stop
* "Atsaukts" canceled stop
[Darbība] - Not needed


What i need is that Price calculates automaticaly, ether wit formula or VBA. Logic should go like this :

module1
IF(OR(A1 = company1,A1=company2))
module2
checks the coresponding row data ([Sektors],[Planning])
module3
calculates price



So what is possible ?

Try:

Please Login or Register  to view this content.

adjust the $100 to the last row number under all your company tables...

Then copy formula down

Thanks!

It works great, but I would like to ask some help (in order to actualy understand fuction myself)

Please Login or Register  to view this content.

I divided code in segments to better understand.

questions:
1. Why is "+1" in the end ?



2. As I understand First Index finds and divides data masive in company segments, then for a row number formula picks a match function because there are 4 sectors to choose from, but I cant realy figure out this part, why so complex

"MATCH(B7,INDEX($K$1:$K$100,MATCH(A7,$K$1:$K$100,0)):$K$100,0)"


3.

"MATCH(F7,$K$2:$P$2,0)+1" This line picks the column, and it has that "+1" in the end.



OK while typing I figured tat out. My prices are = index column +1



Thanks a lot anyhow !


EDIT:


A new problem.

field [Sektors] has lookup function. The price formula does not reckognize value in [Sektors] and returns #N/A

can this be fixed ?

Solved !

Please Login or Register  to view this content.

On picking MATCH for row input "VALUE(according cell)"