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 !
-----------------------------------------------------------------------------------------------

"DC" wrote in message
...
Excellent Pete, well spotted

not sure you meant to include the negative signs though.

Many thanks

"Pete_UK" wrote:

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(VL OOKUP(K16,nonpr,
3,FALSE)),"",VLOOKUP(K16,nonpr,
3,FALSE)*1000),if(iserror(VLOOKUP(K16,nonpr,3,FALS E)),-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


On May 30, 3:35 pm, DC wrote:
Thanks Bob, but I still get the 'too many arguments' message.



"Bob Phillips" wrote:
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)

"DC" wrote in message
...
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(VL OOKUP(K16,nonpr,3,FALSE))-,"",VLOOKUP(K16,nonpr,3,FALSE)*1000,if(iserror(VLO OKUP(K16,nonpr,3,FALSE)),-VLOOKUP(K16,pr,3,FALSE),VLOOKUP(K16,pr,3,FALSE)+(V LOOKUP(K16,nonpr,3,FALSE)-*1000))))-
Hide quoted text -

- Show quoted text -