Nested IFERROR multiple Vlookup

[Kmitchell]

Hi - below is my formula that I can not get to work without receiving an error message. Am I missing a parethesis or do I have too many?

Formula should sequencially look up three columns, and if return value is greater than K2 then return K2.


IF(IFERROR(IFERROR(IFERROR(VLOOKUP($U2,'MAX RATE'!$D:$R,15,FALSE)),VLOOKUP($V2,'MAX RATE'!$D:$R,15,FALSE),VLOOKUP($W2,'MAX RATE'!$D:$R,15,FALSE))>$k2,$k2,IFERROR(IFERROR(IFERROR(VLOOKUP($U2,'MAX RATE'!$D:$R,15,FALSE)),VLOOKUP($V2,'MAX RATE'!$D:$R,15,FALSE)

 

[Eric W]

There's often a better way to create a formula than repeating a large section twice, if only by using helper cells. In your formula above, the first part has VLOOKUP 3 times, and the second time VLOOKUP appears 2 times only, so I'd suspect that might be the issue.

However, this array formula might work for you:

=IFERROR(MIN(VLOOKUP(OFFSET($U2,0,SMALL(IF(ISNUMBER(MATCH($U2:$W2,'MAX RATE'!$D:$D,0)-1),COLUMN($U2:$W2)-COLUMN($U2)),1)),
'MAX RATE'!$D:$R,15,FALSE),$K2),"No match")

Confirm with Control+Shift+Enter.

 

[Kmitchell]

Thank you! Very sophisticated. I would have never figured it out without your help.