IF and VLOOKUP formula for different sheets

[artemisa34]

Hi all,

I am trying to search a number along four sheets. The formula I am using is this one:

=IF(VLOOKUP(M2;'ETL SMS'!N:N;1;FALSE)=Casos!M2;Casos!M2;VLOOKUP(Casos!M2;'ETL UMS'!N:N;1;FALSE)=Casos!M2;VLOOKUP(M2;'ETL FXS'!N:N;1;FALSE)=Casos!M2;VLOOKUP(M2;'ETL OIS'!O:O;1;0))

Where 'Casos' is the sheet containing the number I have to match, and "ETL SMS", "ETL UMS", "ETLS FXS" and "ETL OIS" the sheets where this number can be found.

Am I using the wrong formula (which is my guess) or is there any error on it that I have missed?

Thanks a lot for the help

 

[Nine Zero]

TRY THIS OUT

=IF(VLOOKUP(Casos!M2,'ETL SMS'!N:N,1,FALSE)=Casos!M2,Casos!M2,IF(VLOOKUP(Casos!M2,'ETL UMS'!N:N,1,FALSE)=Casos!M2,Casos!M2,IF(VLOOKUP(Casos!M2,'ETL FXS'!N:N,1,FALSE)=Casos!M2,Casos!M2,IF(VLOOKUP(Casos!M2,'ETL OIS'!O:O,1,0)=Casos!M2,Casos!M2,""))))

 

[artemisa34]

Thanks Nine Zero. Weirdly, it does work for some numbers but does not found others (and when checking in the ETL sheets, the number is there).

Do you know why is this happening??

 

[Nine Zero]

It could be a formatting issue with your values try checking that, or maye the column you are referencing check that as well , i dont see any other reason

 

[artemisa34]

I found what the problem is: the formula only returns the values found in the first sheet that it looks up, in this case 'ETL SMS'. So if I insert this formula:

=IF(VLOOKUP(Casos!M2,'ETL OIS'!N:N,1,FALSE)=Casos!M2,Casos!M2,IF(VLOOKUP(Casos!M2,'ETL UMS'!N:N,1,FALSE)=Casos!M2,Casos!M2,IF(VLOOKUP(Casos!M2,'ETL FXS'!N:N,1,FALSE)=Casos!M2,Casos!M2,IF(VLOOKUP(Casos!M2,'ETL SMS'!O:O,1,0)=Casos!M2,Casos!M2,""))))

It will only return values found in 'ETLS OIS', and the rest as #N/A.