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, 12 views)