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:
Code:
=IF(ISNA($F13),"Missing",IF(ISNA($G13),"",$G13))
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.