Vlookup Error

[JadonR]

I am using the following formula:

=IF(A$24="Yes",(VLOOKUP(Y3,'Purchasing Units'![B]A:B[/B],1,FALSE)),"1")

I would like the vlookup to start at A3 instead of the entire A column, so I tried the following:

=IF(A$24="Yes",(VLOOKUP(Y3,'Purchasing Units'![B]A3:B50[/B],1,FALSE)),"1")

When I change the A:B to A3:B50, I get a #NA error (or sometimes #NAME). What am I doing wrong?

 

[Matty]

With either formula, with the col_index_num set to 1, it will be looking in Column A, not Column B. Is that what you intended?

Matty

 

[JadonR]

Yes, that's correct.

 

[T. Valko]

I am using the following formula:

=IF(A$24="Yes",(VLOOKUP(Y3,'Purchasing Units'![B]A:B[/B],1,FALSE)),"1")

I would like the vlookup to start at A3 instead of the entire A column, so I tried the following:

=IF(A$24="Yes",(VLOOKUP(Y3,'Purchasing Units'![B]A3:B50[/B],1,FALSE)),"1")

When I change the A:B to A3:B50, I get a #NA error (or sometimes #NAME). What am I doing wrong?

Can you explain in words what you're wanting to do?

If you're using column index number 1 then you only need to use column A in your table array argument.

Also, you should not quote numbers to be the results of a formula. Quoting numbers turns them into TEXT strings which may lead to problems if you have to do calculations on the results of this formula.

 

[JadonR]

Ok, I would like to use a vlookup to get a number from Col A on the 'Purchasing Units' tab. If it doesn't match any of the numbers from cells A3 through A50, then I want it to return the number 1.

And of course I only want it to do this if A24 = Yes.

 

[T. Valko]

Ok, I would like to use a vlookup to get a number from Col A on the 'Purchasing Units' tab. If it doesn't match any of the numbers from cells A3 through A50, then I want it to return the number 1.

And of course I only want it to do this if A24 = Yes.

OK, try this...

=IF(A24="Yes",IF(COUNT(MATCH(Y3,'Purchasing Units'!A$3:A$50,0)),Y3,1),"")

 

[JadonR]

That did the trick. Thanks!

 

[T. Valko]

That did the trick. Thanks!

You're welcome. Thanks for the feedback!

 

[snoopyhr]

I realized the solution is already given, just wanted to offer another solution.

=IF(A24="yes",IF(ISNUMBER(MATCH(Y3,'Purchasing Unit'!$A$3:$A$50,0)),"",1))

 

[T. Valko]

I realized the solution is already given, just wanted to offer another solution.

=IF(A24="yes",IF(ISNUMBER(MATCH(Y3,'Purchasing Unit'!$A$3:$A$50,0)),"",1))

The logical test =IF(A24="yes" doesn't have a value_if_false argument so will default to FALSE.

If the lookup value is found then return the lookup value otherwise return 1.