I think you have one of the right parentheses in the wrong place, Catbert. But before that, I have a problem—I think—in the VLOOKUPs themselves. There are three of them, and it looks like each one looks in A1:A7 for a value matching what's in A13. The first VLOOKUP returns what's in column A—that is, the lookup value itself. The second returns the corresponding value in B and the third in C. Now Excel won't tell you, but I think that first one has to be wrong. A13=VLOOKUP(A13,$A$1:$C$7,1,FALSE) is asking "Look up A13 in A1:A7. If the value is there, is it equal to A13?" It's redundant, you see; finding out whether it's there at all is what VLOOKUP does, so there's no need to check again.
Judging by your description, what you wanted was to double check to make sure that A13 actually appears in the table. I'll explain how to do that, too, but first, the next problem (and the only other one I see) is that misplaced paren. If I'm reading this right, your formula says this:
1) Look up A13 in A1:A7. Is it equal to itself?
2) Look it up again; is the corresponding value in B "Facial"?
3) Look it up a third time; is the corresponding value in C True?
If all three conditions are true, then display "".
What you want to accomplish is this: {Look up A13 in A1:17. Is it there? And is the corresponding value in B "Facial"? If both are true, display the corresponding value from C; otherwise display ""} You had the right idea with the AND function, but you accidentally put the third VLOOKUP inside the AND parentheses, and then there's that problem with the first lookup too. So, one at a time:
1) To ask whether the VLOOKUP was successful—that is, to ask Excel whether A13 was actually in the table—use the ISNA function:
Code:
=ISNA(VLOOKUP(A$13,A$1:C$7,3,FALSE))
ISNA checks a result to see whether it's the #N/A value; it returns True if A13 is
not found in the table, you see, and False if it's there. If you want it the other way around, use NOT(ISNA(VLOOKUP(etc))).
2) Your AND function was about right, it just had that misplaced paren as I said. You want an AND condition that specifies that A13 is in the table and that column B is "Facial", so one way to do it is this:
Code:
=AND(NOT(ISNA(VLOOKUP(A$13,A$1:C$7,1,FALSE))),VLOOKUP(A$13,A$1:C$7,2,FALSE)="Facial")
3) Lastly, if the AND condition is true you want to look up what's in the third column, otherwise display "", so wrap that IF condition around it:
Code:
=IF(AND(NOT(ISNA(VLOOKUP(A$13,A$1:C$7,1,FALSE))),VLOOKUP(A$13,A$1:C$7,2,FALSE)="Facial")),VLOOKUP(A$13,A$1:C$7,3,FALSE),"")
Notice the one change in the parentheses.
This leaves me dissatisfied with just one thing: You're looking up each value three times, when you need it only twice (once for col B and once for C). With just a few rows it doesn't matter; you won't notice the difference. But if you try to do this in a worksheet with hundreds or thousands of rows, you'll find it's a lot slower. In that case I'd have it do the lookup in a helping column; then evaluate the helping column to see whether it's ISNA, and if not, display the value. Heck, use the MATCH and OFFSET functions instead of VLOOKUPs and you can do just one lookup instead of two or three. But that's up to you; I just get picky about such details.