Help w/nested IF statement

[rcrecelius]

I have this formula working as is but it needs 1 more "IF" added to it...Ive never created a formula this lengthy but I thought I could do 7 "IF's" without a problem.
here is the formula...
=IF(VLOOKUP(H2,A:E,5,TRUE)="M",IF(VLOOKUP(H2,A:G,7,TRUE)="A",(VLOOKUP(H2,A:F,6,TRUE)/100)*O2),
IF(VLOOKUP(H2,A:G,7,TRUE)="E",(VLOOKUP(H2,A:F,6,TRUE)/100)*O2))

 

[SkipVought]

Hi,

Is there a question in there struggling to get out? I sure don't see one.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[AnotherHiggins]

There is a limit of 7 nested functions.

Look up "specifications" in Excel's help file for more limitations.

Explain the logic of what exactly you are trying to accomplish. There's probably a better way to do it than nested functions.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[rcrecelius]

Sorry, struggle is exactly what I'm doing as I have a deadline of right now on this project...I know vlookups well, other things not so well so I'm sure there is a better way to achieve the desired outcome.
I am trying to create a price file by extracting data out of the database and marrying the Pricing Plan data and the Inventory data.
Pricing Plan data is this...
LineGroupPricingCode all together in Col A, then Line, Group, Pricing Code, Markup/Down, Percent, Price Field
Inventory data is
LineGroupPricingCode all combined into ColH, then Line, Group, Pricing Code, Part NUmber, Desc, and the Price Fields that will be used for calculating this customers actual cost.
The first IF is looking for a particular Line Group and Pricing Code and returning the Markup/Down value(either M or G)...the 2nd IF is looking for the Price Field to be used(A, E, or R)...note, using True in the vlookups because it is possible for the INventory data to have a Pricing Code that does not aply to this customer(Pricing Code would be blank in the Pricing Plan data).

Maybe it would be easier to read like this...
If Markup/Down = "M"
If Price to be used = A
Multiply Price A * (Percent/100)
else
If MArkup/Down = "M"
If Price to be used = "E"
Multiply Price E * (percent/100)
else

Wait a minute...would it make more sense to combine some of these with an "AND"?

 

[stubnski]

Try checking out sumproduct


Stubnski

 

[SkipVought]

This looks very convoluted to me. Let's try to clarify.

"extracting data out of the database"
What is the database?
How is the data, presumably Pricing and Inventory, extracted?
How is the extracted data imported to Excel?

I have manhy more questions, but lets start with these.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[rcrecelius]

Im sorry for the lack of detail in my questions...I was overwhelmed and really needed this finished yesterday and was scrambling to find an answer. Funny but now that the deadline is past I feel better about it.
Database is a Unix platform running Activant Prism software...I can create reports in there, open it in a viewer and save as a text file...then import into Excel. I tried creating what I need in the Prism but was having some difficulty and opted to look for an Excel solution.

 

[SkipVought]

So is there ONE text file or TWO; one for Pricing and one for inventory?

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[SkipVought]

Oh, yes, and did this requirement go away with the deadline or do you still need an answer?

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[rcrecelius]

Text files - 2...1 with Pricing data, the other w/inventory.

Hehe...yes, still looking for an answer...I'll be in meetings most all day today and tomorrow and I'd told the customer that I'd have him something by the end of the week so the deadline was somewhat self-imposed.

Actually I think I've got something that will work now..I dont know why I was trying to cram all that into 1 formula.
I added a column to do some of the math so now my main formula is down to...
=IF(AND(G2="M",H2="A"),K2*I2,IF(AND(G2="M",H2="E"),L2*I2,N2/I2))

 

[SkipVought]

Import each file into a separate sheet.

Use MS Query on a separate sheet to JOIN on the LineGroupPricingCode.

faq68-5829

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}