if(iserror(vlookup.... question

[noboffinme]

HI

I need to have a formula perform a vlookup for a value in a spreadsheet & return a value (as vlookups usually do) however, if an error comes up, I want another vlookup value from another spreadsheet shown.

It goes something like this;

IF(ISERROR(VLOOKUP(A2,'SHEET1'!,A1:B50,2,FALSE)),VLOOKUP(A2,'SHEET2!,A1:B50,2,FALSE)

I keep getting 'FALSE' as the answer when one or the other of the values should be displayed.

 

[Bilbo_Baggins_Esq]

well, I'm assuming you typed that formula here instead of pasting it from your worksheet because your should not have a comma between 'SHEET1'! and A1:B50. That would give the VLOOKUP too many arguments and result in an error.

when using a ISERROR test, I normally provide the result I expect.
I do not know if it is required, but I always do it anyway.

such as
IF(ISERROR(VLOOKUP(A2,'SHEET1'!A1:B50,2,FALSE))=TRUE,TRUE,FALSE)


However, assuming that is not the actual reason you're getting a false, I notice you have not specified a If false argument for your IF test.


IF(ISERROR(VLOOKUP(A2,'SHEET1'!,A1:B50,2,FALSE)),VLOOKUP(A2,'SHEET2!,A1:B50,2,FALSE)


I believe this means that your ISERROR test is not returning TRUE, so it is using the FALSE argument of the IF and since you have specified that argument to be FALSE, it is returning FALSE.

Presumably, if the ISERROR (with unspecified result) returns TRUE, you want the ALTERNATE VLOOKUP in the TRUE argument and non-alternate VLOOKUP in the FALSE argument.

 

[Brianwarnock]

Besides all that Bilbo said, there was also a closing ) missing in the original.

A working version would be

=IF(ISERROR(VLOOKUP(A2,Sheet1!A1:B50,2,FALSE)),VLOOKUP(A2,Sheet2!A1:B50,2,FALSE),VLOOKUP(A2,Sheet1!A1:B50,2,FALSE))

I post it because with the number of errors in the original I'm not confident of Bilbo's explanation being understood.

Brian

 

[noboffinme]

Thanks to Bilbo_Baggins_Esq & Brianwarnock for answering this one

I did write that formula freehand, hence the errors so thanks for understanding th overall issue.

That has saved me a lot of work, cheers