youve entered too many arguments

[maywal]

B

 

[Special-K99]

After your IF D3=COVENTRY you have VLOOKUP Coventry if true or VLOOKUP YTS BTL if false.

After that you ve placed your new underlined IF

Your D3=COVENTRY already has a true and false argument so your new IF is one argument too many.

Perhaps place it before the last VLOOKUP ?

 

[Special-K99]

Try (untested)

=IFERROR(IF($D3="HSBC",VLOOKUP($I3,HSBC!$A:$F,MATCH($E3,HSBC1,0),FALSE),IF($D3="YBS",VLOOKUP($I3,YBS!$A:$H,MATCH(E3,YBS,0),FALSE),IF($D3="LBG",VLOOKUP($I3,LBG!$A:$H,MATCH($E3,LBG,0),FALSE),IF($D3="COVENTRY",VLOOKUP($I3,COVENTRY!$A:$I,MATCH($E3,COVENTRY,0),FALSE),IF($D3="NATIONWIDE",VLOOKUP($I3,NATIONWIDE!$A:$G,MATCH($E3,NATION,0),FALSE),VLOOKUP($I3,'YTS BTL'!$A:$H,MATCH($E3,YTSBTL,0),FALSE),))))),"")

 

[Jonmo1]

The last vlookup of your original formula was just put in the ValueIfFalse part of the last IF.
You then just added another IF after that..

So original formula was like
=IF(x="a",VLOOKUP(1),IF(x="b",VLOOKUP(2),VLOOKUP(3)))

See how VLOOKUP(3) is in the False part of the 2nd IF. It assumes that x="c", without actually testing with an IF.
You then did this
=IF(x="a",VLOOKUP(1),IF(x="b",VLOOKUP(2),VLOOKUP(3),IF(x="d",VLOOKUP(4))))

That 3rd IF is now in the 4th argument of the 2nd IF. Of coarse there can't be 4 arguments in an IF.
That would have to be written like this
=IF(x="a",VLOOKUP(1),IF(x="b",VLOOKUP(2),IF(x="c",VLOOKUP(3),IF(x="d",VLOOKUP(4)))))

 

[Jonmo1]

All the other sheets and Named Ranges have matching names..
Except HSBC/HSBC1 and Nationwide/Nation

If you could make those 2 the same as well, both Sheet and Named range named HSBC and Nation, then you can greatly simplify with indirect

=IFERROR(VLOOKUP($I3,INDIRECT("'"&$D3&"'!$A:$I"),MATCH($E3,INDIRECT($D3),0),FALSE),"")