VLOOKUP with IF and AND

[DiscoJean]

I currently have the following formula which is saying that I've entered too few arguments for this function.

How would I go about fixing it? Is there an easier way?

=IF(AND(VLOOKUP('TAB1'!C2:C500=1,'TAB1'!D2:D500="yes"),"in use","available")

I want it to check the TAB1 sheet in column C to see if it returns either 1, 2, 3, 4, 5, 6, 7 or 8 (entered manually - no formula) (there will be other cells focusing on 2, 3, 4 etc.) and column D to see if that slot says "yes".

I want it to say "in use" if both conditions are fulfilled, and "available" if column D is not fulfilled.

Any help will be most appreciated, particularly if I'm using the wrong formula!

 

[DiscoJean]

I have changed it to the following:

=IF((AND((VLOOKUP(C1,'TAB1'!$D$2:$E$500,1,FALSE)),(VLOOKUP("yes",'TAB1'!$D$2:$E$500,2,FALSE)))),"in use","available")

But am getting #N/A as there is not data in all of the fields as we will add it as time goes by.

I'm trying to add the ISNA formula into this, but am unsure as to where to insert it.

 

[Joe4]

So, are you just trying to check to see if the value in cell C1 exists in column D on TAB1, and that the value in column E one that same row is equal to "yes", and if so, return "in use", else return "available"?

If so, try this formula instead:

=IF(COUNTIFS('TAB1'!D$2:D$500,C1,'TAB1'!E$2:E$500,"yes"),"in use","available")

 

[DiscoJean]

That was what I'm trying to do.

Your suggested formula works perfectly!

Thank you!

 

[Joe4]

You are welcome.