nested IF(ISERROR()) statement

[Guest]

I get an 'you've entered too many arguements' error message with my formula
below. What do I need to do to get it to work?

I have 2 tables, PR and nonPR, and I want to add values together where the
left most strings match in each. If only one table contains the string then
take that value, if neither do then return a blank.

=IF(ISERROR(VLOOKUP(K16,pr,3,FALSE)),if(iserror(VLOOKUP(K16,nonpr,3,FALSE)),"",VLOOKUP(K16,nonpr,3,FALSE)*1000,if(iserror(VLOOKUP(K16,nonpr,3,FALSE)),VLOOKUP(K16,pr,3,FALSE),VLOOKUP(K16,pr,3,FALSE)+(VLOOKUP(K16,nonpr,3,FALSE)*1000))))

 

[Bob Phillips]

Put the VLOOKUP formula in their own cells and reference these cells.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Bob, but I still get the 'too many arguments' message.

 

[Pete_UK]

Your problem is after the first 1000, as you are trying to have
another parameter after the false argument of the IF. Try this
amendment:

=IF(ISERROR(VLOOKUP(K16,pr,3,FALSE)),if(iserror(VLOOKUP(K16,nonpr,
3,FALSE)),"",VLOOKUP(K16,nonpr,
3,FALSE)*1000),if(iserror(VLOOKUP(K16,nonpr,3,FALSE)),­VLOOKUP(K16,pr,
3,FALSE),VLOOKUP(K16,pr,3,FALSE)+VLOOKUP(K16,nonpr,3,FALSE)­*1000))

Hope this helps (and that I've counted all the brackets correctly)

Pete

 

[RagDyeR]

See if this works for you:

=IF(ISNA(VLOOKUP(K16,PR,3,0)),IF(ISNA(VLOOKUP(K16,nonPR,3,0)),"",VLOOKUP(K16,nonPR,3,0)*1000),IF(ISNA(VLOOKUP(K16,nonPR,3,0)),VLOOKUP(K16,PR,3,0)*1000,(VLOOKUP(K16,PR,3,0)+VLOOKUP(K16,nonPR,3,0))*1000))
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Thanks Bob, but I still get the 'too many arguments' message.

 

[Guest]

Excellent Pete, well spotted

not sure you meant to include the negative signs though.

Many thanks

 

[RagDyeR]

Make sure that you adjust Pete's formula to multiply the PR range by 1000 if
the value is found in *both* ranges.

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Excellent Pete, well spotted

not sure you meant to include the negative signs though.

Many thanks

 

[Pete_UK]

The negative signs are to do with word-wrap on the newsgroups - no,
they should not be included (and they don't show on Google Groups).

Glad it worked for you - thanks for feeding back.

Pete

 

[Pete_UK]

In the original formula it was only the nonpr range which was
multiplied by 1000.

Pete

 

[Ragdyer]

How right you are!

One good thing about getting old ... you can then blame your stupid mistakes
on old age too.<bg>

 

[Gord Dibben]

Pick me......pick me>g>

Gord

 

[Bob Phillips]

Is that an offer for us all to blame our mistakes on you Gord?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JMay]

I'm confused here before anyone else in the thread is..
I'm looking for the FIRST action to be taken if the FIRST IF is true...
=IF(ISERROR(VLOOKUP(K16,pr,3,FALSE)), What to do if true goes here,
But there is nothing spelled out to do !!
(then and only then) should come the (creating a NESTED IF STATEMENT)
if(iserror(VLOOKUP(K16,nonpr,3,FALSE)),"" <<< Ok, here I see the
action to be taken if the second If statement is TRUE..

Does anyone else see my reason for confusion?

Thanks in advance.

Jim

 

[JMay]

Ok, reconsidering things here, I guess you can just as easily have a
nested if statement which says... If(TRUE,If(),otherwise whatever) --
If right, then Sorry for posting unnecessarily.