I'm looking for a formula for C13 that will search Column A for the employee ID listed in A13-A17, and IF it finds that value in A1:C7 AND IF Column B says "Facial", then it will return the value from Column C for that employee's facials. If not, then it should remain blank.



Here's what I've come up with, but it's not working right.
=IF(AND(A13=VLOOKUP(A13,$A$1:$C$7,1,FALSE),VLOOKUP (A13,$A$1:$C$7,2,FALSE)="Facial",VLOOKUP(A13,$A$1: $C$7,3,FALSE)),"")

This is my first post so I hope I've attached the sample file correctly.

Any assistance with this is greatly appreciated!

Attached Files

sample sheet.xls (18.0 KB, 12 views)

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: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: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: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.

Thank you, BobBridges. Unfortunately, when I copy/paste your formula, all I set is "The formula you typed contains an error." Any suggestions as to what this error could be?

It would have been clever of me to leave an error in there, to make sure you were actually thinking about it and not just cutting and pasting. (If you don't think about it, you won't be able to do it for yourself the next time.) But no, the error was inadvertent; looking at it now, I'm pretty sure I have one too many right parentheses.

I think I see which one, too. However, it's never too late to lead the student to water: You look at it, and figure out which right paren doesn't belong. I'll help, if you come up with questions, but "I don't see it" isn't one of the questions; you have to show me you're thinking about it.

I like the way you operate, BobBridges.

I've removed one paren, immediately following "Facial" and the $ from the A13 reference so I can copy the formula down:
=IF(AND(NOT(ISNA(VLOOKUP(A13,A$1:C$7,1,FALSE))),VL OOKUP(A13,A$1:C$7,2,FALSE)="Facial"),VLOOKUP(A13,A $1:C$7,3,FALSE),"")

The formula is now returning a correct value, except for Employee BAYBE, who is in the list twice, for Nail as well as Facial. How do we tweek this formula to return the value in C for the row that contains "Facial" on employees who are listed more than once?

Hm, you're right about A$13; I didn't think that through. I plead lack of practice; I myself use R1C1 reference style, which is more intuitive for a programmer type like me. That formula in R1C1 would look like this:And yes, I agree with you about the extra paren.

Now about BAYBE. I didn't understand at first what was happening; if BAYBE is in there twice, it's still showing only the record for Facial, right? But no, I see the problem; if the record for "Nail" is before the one for "Facial", then this formula will find the one for Nail and stop looking, then display blanks because it's not "Facial". Is that the way it's behaving?

The solution will be to add the complication of a helping column. But while I was testing my solution, I found that there's a problem with the formula we agreed on. And since you didn't mention it, I suspect I've misunderstood your setup. Before we go on, maybe I should show you what I think you said, and you tell me what you actually are using. I'm attaching a test workbook; you'll notice in row 16 that when I put in a name that doesn't appear in the table, Excel displays not blanks, as was intended, but #N/A. That's because of the second lookup, you see. Now, there are ways around that, but why didn't you complain about it? I can think of two reasons: 1) You don't happen to have put any names in rows 13-17 that aren't in the table, or 2) my test worksheet doesn't match what you're actually doing. So I think I'd better check before we go any further.

Attached Files

x.xlsx (8.4 KB, 10 views)

The #N/A is not a problem because it will alert me that someone has removed an employee from the original list without submitting a termination. I need the blanks to appear if the Empl ID is found, but not the service. I have to admit that I've never used a helping column and have no idea how to. Looks like I have something to research.

I must admit I did not have the courage to read all the preceding posts so I may have missed something

The following should do what you need ( based on your first post AND if you are using XL2007 as stated). It does have to be entered as an array formula with Ctrl+Shift+Enter and you'll have to change the " facial" part if needed

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Oh, "helping column" is just a common term that means you put intermediate results out in a separate column, usually somewhere to the right, for use not by human eyes but by further Excel formulae. Some people hide them by putting them so far to the right that they're off-screen. Some actually hide them, that is, reduce the column widths to 0. My choice, if I keep the worksheet around long enough, is to make all the cells grey except the columns at the left that I'm actually using; the slightly darker cells to the right are a visual indication that I can look at those cells for reference but am not expected to change them—not, at least, without very good reason. Your choice depends mostly on whether you intend this worksheet to be used by others or only yourself.

So here's what I have in mind for your helping columns. Keep in mind that there are a lot of ways to do this, so if you don't like my solution, feel free to complain and we can figure out an alternative that you like better.

For this method you'll put your helping column not somewhere out to the right, as usual, but in column C, moving what's already in C to D. If you have a problem with this solution, that's probably where it'll be. As I said, there are other ways, but bear with me for now.

1) So, first you insert column C, causing what's already there to move to D. Then put in C1:C7 a formula that looks like this:Take a look at the attached sample to see the result.

(Why the extra character? With me it's a habit that I cultivated years ago to preclude a specific problem; I don't think it can arise in this particular worksheet, so if you want to ignore it you should be safe.)

Ok, that's one helping column. Maybe you can already see that this is the value you'll check with your final VLOOKUP. But for this attempt I'm going to abandon the AND function and use two more helping columns, this time for your rows 13-17:

2) In column F (just for example), put a VLOOKUP to see whether the name is in the table: =VLOOKUP($A13,$A$1:$A$7,1,0)

3) In column G, look up the name plus "/Facial": =VLOOKUP($A13&"/Facial",$C$1:$D$7,2,0)

Now back in what used to be C17—only it's D17 now—check those two values display one of three possible results:Notice the outcome:
- If the name isn't on the table at all (that's ISNA($F13)), what shows up in D13 is "Missing"
- If the name is there but "<name>/Facial" isn't, then display blanks.
- If the name is there and "<name>/Facial" is there too, then display what's in column D.

Once you're satisfied with the result, you can hide column C and, if you like, columns F and G. That's what I did, in the attached sample.

Or, if you don't like putting the concatenated <name>/<type> in column C, we can put it out to the right. But in that case we can't use VLOOKUP; we'll have to use MATCH and OFFSET instead. Me, I would have gone with that in the first place. But I didn't want to confuse the issue by adding more functions, unless you want to try them out.

Attached Files

x.xlsx (8.8 KB, 13 views)

Pecoflyer, your formula is absolutely PERFECT! Thank you so very, very much.

Arg, my thunder stolen yet again! I just gotta learn this INDEX thingy; I'm not sure I've ever used it. Pecoflyer, I'll take a closer look at this formula and if I can't figure it out, I may have to ask for help understanding it.

AFAIK the best place to start to learn the INDEX function is http://www.excelhero.com/blog/2011/0...ing-index.html

Very powerful and most users do not use it's full capability. The most important is to remember that INDEX returns a cell or range REFERENCE behind the screens.
This being said, my suggested solution is an array formula which might slow things down in case of large sheets

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I'm working my way through that (rather long) article now. I can already see one improvement in future lookups. One problem with VLOOKUP is that the target column must be to the right of the index column; wherever that's not the case, I used to use MATCH with INDIRECT to construct a pointer to the desired value. I recently realized that OFFSET is better than INDIRECT. But INDEX: is better yet. I'll keep reading.

Oh, and by the way, I never knew about the IFERROR function. That's marvelous!

Find attached the result of the formula

Attached Files

Sample Sheet.xlsx (13.3 KB, 15 views)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Very curious! First, I more or less assumed that we'd be able to attach samples in private messages as well as here, but I don't see a way. Second, I did use <Ctrl-Shift-Enter>, and got the results I described.

In fact, I opened your worksheet, selected C13:C17, hit <F2>, then hit <Ctrl-Shift-Enter>—and the results changed to display 3049 in all five cells! I've attached the results. Out at the right I carefully pasted the formulae before and after my action; the formulae are identical, but as soon as I hit <Ctrl-Shift-Enter> (without changing the formula) it made that change.

So apparently either my copy of Excel isn't doing something right, or more likely I'm not doing something right. Can you think of some pointed questions that'll help me figure out what?

Oh, and just in case, I closed that worksheet and reopened it; still the same value in all five cells.

Attached Files

x.xlsx (11.4 KB, 11 views)