Trying to avoid nested IFs

[Alonso]

Here is a formula I'm using

=IF(B5=B4,IFERROR(HLOOKUP(VLOOKUP(B5,Referencias!$BG$1:$BH$19,2,FALSE),Catalogo!$K$1:$Y$7,(IF(VLOOKUP(L5,Catalogo!$C$2:$F$1117,4,FALSE)<>(VLOOKUP(L4,Catalogo!$C$2:$F$1117,4,FALSE)),5,(IF(L5<>L4,IF(VLOOKUP(L5,Catalogo!$C$2:$F$1117,2,FALSE)>(VLOOKUP(L4,Catalogo!$C$2:$F$1117,2,FALSE)),6,IF(VLOOKUP(L5,Catalogo!$C$2:$F$1117,2,FALSE)<(VLOOKUP(L4,Catalogo!$C$2:$F$1117,2,FALSE)),7,2)),IF(VLOOKUP(L5,Catalogo!$C$2:$F$1117,2,FALSE)>(VLOOKUP(L4,Catalogo!$C$2:$F$1117,2,FALSE)),4,IF(VLOOKUP(L5,Catalogo!$C$2:$F$1117,2,FALSE)<(VLOOKUP(L4,Catalogo!$C$2:$F$1117,2,FALSE)),3,0)))))),FALSE),0),0)

I know, I know
it's toooooo long

what I'm doing is comparing a machine with the last one used, then seen if
there is a swicth on the material that it needs. If true, this formula will
find how long it´s going to take finding the values in a table.

seems complicated because it search trought several tables


could anyone think on an easier way??

 

[JLatham]

First question: Does it consistently provide you with valid results?

If the answer to that is YES, then leave it alone.

I see some possibilities of reducing it to some degree MAYBE by using
SUMPRODUCT() to return values out of the Catalogo!$C$2:$F$1117 table. But
I'm not wading into this any deeper if your answer to my question was YES.
If the answer was YES, are there other issues such as taking too much time to
return a result?

 

[ryguy7272]

That is a CrAzY function. Try sumproduct:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Also, try using a Pivot Table:
http://www.contextures.com/xlPivot02.html
http://peltiertech.com/Excel/Pivots/pivottables.htm

Regards,
Ryan---

 

[Alonso]

thanks for your reply J

the answer to your question is YES
this formula works fine

the main issue to try to simplify this
it's because it has reached the nesting limit

altroght not critical, i was trying to include another IF to check another
data
right now, I can't do that

I'll try with the sumproduct
and get back with news

 

[Pete_UK]

You've got IFERROR in there, which implies that you are using Excel 2007,
which I thought had a nesting limit of 64 - your formula does not exceed
that, does it? (I've not looked at it in detail as I only have Excel 2000)

Pete

 

[Alonso]

Hi Pete

You're right
I'm using Excel 2007

the reason i got the nesting error is because I save this archive in 97-2003
format
not everyone in the office has 2007, and I need that they can see the file

it's there a potencial problem with the IFERROR formula??

 

[Pete_UK]

Excel 2003 and earlier versions will not recognise IFERROR, so you will
probably have a #NAME error if you try to load the file on those versions.

With regard to the nesting problem, I suggest you split your formula into
smaller chunks over a few columns and then combine them using these
intermediate columns.

Hope this helps.

Pete

 

[Alonso]

thanks Pete
probably that will work
split in 2 in columns
and get the max between those??
after all
the formula gets only 1 value

 

[Pete_UK]

Glad to be of help.

Pete

thanks Pete
probably that will work
split in 2 in columns
and get the max between those??
after all
the formula gets only 1 value