IF combined with VLOOKUP throwing up the "too many arguments for this function" reply

[dezibluenose]

Hi all. Looking for some help with this issue and also to improve my abysmal excel knowledge here.

Last year, I created a formula in my work to allow us to check if a customer existed on one of our two tracker files. We had a main file with customer numbers and the formula would use a vlookup in the first log and if it got no results it would check the second log or return nothing.

This was the formula I used:-

=IFERROR(IF(VLOOKUP(A1,'[Home Log Year 1.xlsm]Main'!$B:$V,20,0)=0,"",
VLOOKUP(A1,'[Home Log Year 1.xlsm]Main'!$B:$V,20,0)),
IF(VLOOKUP(A1,'[Away Log Year 1.xlsm]main'!$B:$V,17,0)=0,"",
VLOOKUP(A1,'[Away Log Year 1.xlsm]main'!$B:$V,17,0)))

This was working perfectly - if it couldn't find details in the Home log it checked the Away log and if still nothing was found it returned blank.


I've now been asked to also include checks on the logs for year 2. I'm showing my ignorance here but I figured I could just carry on my IF statement and add the links to year 2, however, when I do that I get the message "You've entered too many arguments for this function". The new formula I'm trying is at the bottom of my post.

I'm obviously missing something here - like I need to connect the searches with an OR statement - or is it simply that I can't do this kind of search over 4 different files? I did try searching here and on line but I'm getting nowhere. Any help anyone can provide to tell Me what I'm doing wrong here would be much appreciated.

New Formula:-

=IFERROR(IF(VLOOKUP(A1,'[Home Log Year 1.xlsm]Main'!$B:$V,20,0)=0,"",
VLOOKUP(A1,'[Home Log Year 1.xlsm]Main'!$B:$V,20,0)),
IF(VLOOKUP(A1,'[Away Log Year 1.xlsm]Main'!$B:$V,17,0)=0,"",
VLOOKUP(A1,'[Away Log Year 1.xlsm]Main'!$B:$V,17,0)),
(IF(VLOOKUP(A1,'[Home Log Year 2.xlsm]Main'!$B:$U,20,0)=0,"",
VLOOKUP(A1,'[Home Log Year 2.xlsm]Main'!$B:$U,20,0)),
IF(VLOOKUP(A1,'[Away Log Year 2.xlsm]Main'!$B:$R,17,0)=0,"",
VLOOKUP(A1,'[Away Log Year 2.xlsm]Main'!$B:$R,17,0))))

 

[Jeffrey Mahoney]

This might be easier to read and use. It also only calls each of the VLOOKUPS once.

=LET(LOG120,VLOOKUP(A1,'[Home Log Year 1.xlsm]Main'!$B:$V,20,0),LOG117,VLOOKUP(A1,'[Away Log Year 1.xlsm]main'!$B:$V,17,0),LOG220,VLOOKUP(A1,'[Home Log Year 2.xlsm]Main'!$B:$V,20,0),LOG217,VLOOKUP(A1,'[Away Log Year 2.xlsm]main'!$B:$V,17,0),Ifs(Log120<>0,Log120,Log117<>0,LOG117,LOG220<>0,LOG220,LOG217<>0,LOG217,True,"")))

 

[Fluff]

@Jeffrey Mahoney those variables are invalid as they are valid cell references.

 

[Jeffrey Mahoney]

I didn't have the workbook to test. The OP can use:
LOGx120
LOGx117
LOGx220
LOGx217

 

[dezibluenose]

This might be easier to read and use. It also only calls each of the VLOOKUPS once.

=LET(LOG120,VLOOKUP(A1,'[Home Log Year 1.xlsm]Main'!$B:$V,20,0),LOG117,VLOOKUP(A1,'[Away Log Year 1.xlsm]main'!$B:$V,17,0),LOG220,VLOOKUP(A1,'[Home Log Year 2.xlsm]Main'!$B:$V,20,0),LOG217,VLOOKUP(A1,'[Away Log Year 2.xlsm]main'!$B:$V,17,0),Ifs(Log120<>0,Log120,Log117<>0,LOG117,LOG220<>0,LOG220,LOG217<>0,LOG217,True,"")))

Thanks for that, but sadly it's not working. There's more than a good chance it's my fault though.


For a bit more context here's pretty much what the sheets I'm working on look like.

Below is the master sheet showing the original formula that was working when checking only two files.

Below is the Home Log Year 1. I know my original formula also refers to Away Log Year 1 and Home and Away logs year 2. I've not included a dump of them here as they look the same as below. Things may be in a different order but the same columns of data as there - just some customers only appear on one log.

The reason for the checks over multiple different files is because some customers will appear on Home Log Year 1, others in the Away year 1, others in the logs for year 2. My bosses want this file set up as a one stop shop so to speak where they can see the application status of all customers in one place.


And just to save people having to go to the top of the thread, here's the original formula that checked two files and does work

=IFERROR(IF(VLOOKUP(A1,'[Home Log Year 1.xlsm]Main'!$B:$V,20,0)=0,"",
VLOOKUP(A1,'[Home Log Year 1.xlsm]Main'!$B:$V,20,0)),
IF(VLOOKUP(A1,'[Away Log Year 1.xlsm]main'!$B:$V,17,0)=0,"",
VLOOKUP(A1,'[Away Log Year 1.xlsm]main'!$B:$V,17,0)))


And here's my attempt at a new one checking four files but giving me the "too many arguments for this function" error.

=IFERROR(IF(VLOOKUP(A1,'[Home Log Year 1.xlsm]Main'!$B:$V,20,0)=0,"",
VLOOKUP(A1,'[Home Log Year 1.xlsm]Main'!$B:$V,20,0)),
IF(VLOOKUP(A1,'[Away Log Year 1.xlsm]Main'!$B:$V,17,0)=0,"",
VLOOKUP(A1,'[Away Log Year 1.xlsm]Main'!$B:$V,17,0)),
(IF(VLOOKUP(A1,'[Home Log Year 2.xlsm]Main'!$B:$U,20,0)=0,"",
VLOOKUP(A1,'[Home Log Year 2.xlsm]Main'!$B:$U,20,0)),
IF(VLOOKUP(A1,'[Away Log Year 2.xlsm]Main'!$B:$R,17,0)=0,"",
VLOOKUP(A1,'[Away Log Year 2.xlsm]Main'!$B:$R,17,0))))

 

[Alex Blakenburg]

I can't test this but based on that you have MS365 and your return value seems to be text, this might work for you:

=XLOOKUP(A1,'[Home Log Year 1.xlsm]Main'!$B:$B,'[Home Log Year 1.xlsm]Main'!$U:$U,
      XLOOKUP(A1,'[Away Log Year 1.xlsm]Main'!$B:$B,'[Away Log Year 1.xlsm]Main'!$R:$R,
         XLOOKUP(A1,'[Home Log Year 2.xlsm]Main'!$B:$B,'[Home Log Year 2.xlsm]Main'!$U:$U,
            XLOOKUP(A1,'[Away Log Year 2.xlsm]Main'!$B:$B,'[Away Log Year 2.xlsm]Main'!$R:$R,
               ""))))
    & ""

 

[Jeffrey Mahoney]

@Alex Blakenburg Nested XLookup formulas. Now that's the first time I've seen that!

 

[dezibluenose]

I can't test this but based on that you have MS365 and your return value seems to be text, this might work for you:

=XLOOKUP(A1,'[Home Log Year 1.xlsm]Main'!$B:$B,'[Home Log Year 1.xlsm]Main'!$U:$U,
      XLOOKUP(A1,'[Away Log Year 1.xlsm]Main'!$B:$B,'[Away Log Year 1.xlsm]Main'!$R:$R,
         XLOOKUP(A1,'[Home Log Year 2.xlsm]Main'!$B:$B,'[Home Log Year 2.xlsm]Main'!$U:$U,
            XLOOKUP(A1,'[Away Log Year 2.xlsm]Main'!$B:$B,'[Away Log Year 2.xlsm]Main'!$R:$R,
               ""))))
    & ""

Absolutely perfect. Thank you so much for this. Now, like I always say on here, I need to break down what you did and how you did it.

Thanks again, really appreciate this.

 

[Alex Blakenburg]

I need to break down what you did and how you did it.

xlookup has a built in function that handles the error condition ie the not_found_condition.
if it didn't have that the you would need to do the same as you do for vlookup and it would look like this.
=IfError( xlookup(lookup_value, lookup_array, return_array) , if_not_found)
however since it does have that function you can drop the external IfError and it looks like this
=xlookup( lookup_value, lookup_array, return_array, if_not_found)
So what you are missing in your original formula is that you need an IfError for each Vlookup.
With Xlookup you replace each Xlookup's if_not_found position with the next xlookup.

If an Xlookup returns a blank it shows zero. If you are returning text values and are not expecting any actual zero values by putting & "" at the end of the formula it will return "" and not convert it to zero.

Hope this helps