VLOOKUP and IFERROR formula issue..

[dannycto01]

Hello, I have an issue.. I'm trying to use the below formula to go through multiple pages and return the found value , etc.. and of course when not found it returns an "#N/A". The formula itself does that, however, .. .. in trying to add ,"No Match") at the end I get the "theres a problem with this formula , blah blah blah ). .. is there something I'm.."missing"?

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP(M19,'3'!$A:$G,2,0),VLOOKUP(M19,'1'!$A:$G,2,0)),VLOOKUP(M19,'0'!$A:$G,0)),VLOOKUP(M19,'Friday May 29'!$A:$G,2,0)),VLOOKUP(M19,'Thursday May 28'!$A:$G,2,0)),VLOOKUP(M19,'Wednesday May 27'!$A:$G,2,0)),VLOOKUP(M19,'Tuesday May 26'!$A:$G,2,0)),VLOOKUP(M19,'Monday May 25'!$A:$G,2,0)),VLOOKUP(M19,'Friday May 22'!$A:$G,2,0)),VLOOKUP(M19,'Thursday May 21'!$A:$G,2,0)),VLOOKUP(M19,'Wednesday May 20'!$A:$G,2,0)),VLOOKUP(M19,'Tuesday May 19'!$A:$G,2,0)),VLOOKUP(M19,'Monday May 18'!$A:$G,2,0)),VLOOKUP(M19,'Friday May 15'!$A:$G,2,0)),VLOOKUP(M19,'Thursday May 14'!$A:$G,2,0)),VLOOKUP(M19,'Wednesday May 13'!$A:$G,2,0)),VLOOKUP(M19,'Tuesday May12'!$A:$G,2,0)),VLOOKUP(M19,'Monday May 11'!$A:$G,2,0)),VLOOKUP(M19,'Friday May 8'!$A:$G,2,0)),VLOOKUP(M19,'Thursday May 7'!$A:$G,2,0)),VLOOKUP(M19,'Wednesday May 6'!$A:$G,2,0)),VLOOKUP(M19,'Tuesday May 5'!$A:$G,2,0)),VLOOKUP(M19,'Monday May 4'!$A:$G,2,0)),VLOOKUP(M19,'Friday May 1'!$A:$G,2,0))

 

[jasonb75]

Just need to add another IFERROR to it

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP(M19,'3'!$A:$G,2,0),VLOOKUP(M19,'1'!$A:$G,2,0)),VLOOKUP(M19,'0'!$A:$G,0)),VLOOKUP(M19,'Friday May 29'!$A:$G,2,0)),VLOOKUP(M19,'Thursday May 28'!$A:$G,2,0)),VLOOKUP(M19,'Wednesday May 27'!$A:$G,2,0)),VLOOKUP(M19,'Tuesday May 26'!$A:$G,2,0)),VLOOKUP(M19,'Monday May 25'!$A:$G,2,0)),VLOOKUP(M19,'Friday May 22'!$A:$G,2,0)),VLOOKUP(M19,'Thursday May 21'!$A:$G,2,0)),VLOOKUP(M19,'Wednesday May 20'!$A:$G,2,0)),VLOOKUP(M19,'Tuesday May 19'!$A:$G,2,0)),VLOOKUP(M19,'Monday May 18'!$A:$G,2,0)),VLOOKUP(M19,'Friday May 15'!$A:$G,2,0)),VLOOKUP(M19,'Thursday May 14'!$A:$G,2,0)),VLOOKUP(M19,'Wednesday May 13'!$A:$G,2,0)),VLOOKUP(M19,'Tuesday May12'!$A:$G,2,0)),VLOOKUP(M19,'Monday May 11'!$A:$G,2,0)),VLOOKUP(M19,'Friday May 8'!$A:$G,2,0)),VLOOKUP(M19,'Thursday May 7'!$A:$G,2,0)),VLOOKUP(M19,'Wednesday May 6'!$A:$G,2,0)),VLOOKUP(M19,'Tuesday May 5'!$A:$G,2,0)),VLOOKUP(M19,'Monday May 4'!$A:$G,2,0)),VLOOKUP(M19,'Friday May 1'!$A:$G,2,0)),"No Match")

 

[DanteAmor]

You can put the list of sheets and add the range of those cells to a named range, for example you call it: Sheetlist
Then you can use an array formula like this:

=IFERROR(VLOOKUP(M19,INDIRECT("'"&INDEX(Sheetlist,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sheetlist&"'!$A:$B"),M19)>0,0))&"'!$A:$B"),2,0),"No match")

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[jasonb75]

@DanteAmor, that would make the formula volatile and less efficient.

The formula contains 23 vlookups (if I counted correctly), using nested iferrors, the formula only needs to process the vlookups until it finds a match, i.e. if the match is in the 3rd sheet then only 3 vlookups are processed, your method means that all 23 are processed every time.