If/then and Vlookup formula

[Guest]

Hi all!

What I am trying to do:
In Sheet 1, column A, I have a list of account numbers. In Sheet 2, Column
A, I have another list of account numbers. If an account in Sheet 1 column
A, is also in Sheet 2, column A, then the account is budgeted; If an account
in Sheet 1 column A is not in Sheet 2, column A, then the account is not
budgeted.

I'm not the cleverest with the formulas, and this is what I have tried and
am not getting the best results:
=IF(A2=(VLOOKUP(A2,Sheet2!$A$2:$B$1072,1,FALSE)),"Budgeted","Acct Not
Budgeted")

It is working for accounts that are budgeted, but for accounts that aren't
budgeted, I'm getting #N/A. I assume this has something to do with the
VLookup.

Any help as to where I'm going wrong would be greatly appreciated. Thank you.

 

[Edward]

Yes. The Vlookup is not finding the lookup value in the specified
range (as you are expecting) and therefore giving a #N/A error. Just
stick an isnumber around a match function. Here's a similar formula.

=if(isnumber(match(A2,Sheet2!$A$2:$B$1072,0)),"Budgeted","Not
Budgeted")

 

[Guest]

Try something like this:

=IF(ISERROR(VLOOKUP(A2,Sheet2!$A$2:$B$1072,1,FALSE))=TRUE,"Acct Not
Budgeted","Budgeted")

-Chad

 

[Guest]

Hi Edward. First and foremost, thanks for your help! I tried this formula
and it didn't work properly either. It gave Not Budgeted for all accounts,
including those that were budgeted.

 

[Guest]

Thank you Chad, this worked perfectly!!

I have never seen the ISERROR before, could you review my understanding
below and see if I'm accurate?

I looked up ISERROR in the help section, and my interpretation is that the
VLOOKUP is still finding an exact match, and if not, it will dispaly the
#N/A. By way of the iserror function, if a #N/A would have been displayed,
as it wasn't in the vlookup, for #n/a's, it is now displaying "acct not
budgeted" and for ones that wouldn't have had an #n/a because it was in the
vlookup, the iserror will not be true, so i'm displaying budgeted. (I hope
that makes some kind of sense)

 

[Edward]

Sorry about that...

Change the B to an A in the formula. Should read:

=if(isnumber(match(A2,Sheet2!$A$2:$A$1072,0)),"Budgeted","Not
Budgeted")

Ed

 

[Dave Peterson]

Since you're only looking at a single column, it makes more sense to me to use
=match().

=if(isnumber(match(a2,sheet2!$a$2:$a$1072,0)),"Budgeted","Acct not Budgeted")