Forum Discussion
Help with XLOOKUP formula to find one cell and either cell b or cell c
Is it possible for you to post a copy (or a mockup) of your actual file layout(s). I appreciate the real file has real names, hence the idea of a mockup with a few silly names (Star War characters are always useful)...
Part of my reason for asking is that I'm having a hard time visualizing where the XLOOKUP formula is vis-a-vis the table in which the names are to be found. And then where the visit time is that is to be changed...in the same table (?). And so on,
I don't think the formula should be all that difficult, but I for one would find it easier to compose if I knew where the XLOOKUP resides and where the data being searched resides.
Frankly, just for data base design reasons, I would recommend that you NOT combine first and last names in a single field. You acknowledge the complications that creates; you're right. It's a lot easier to concatenate when needed (with more flexibility* in doing so) than it is to take apart when that 's needed. And it would make it far easier to manipulate such distinctions as "preferred name" or "nicname" or "legal name"....
*why do I say "more flexibility"? Because if you've got first and last as their own fields,
- you can easily sort all the columns by last name (which, yes, you can do if they're together in a lastname, firstname style"
- you can easily print lists of all the names, using "preferred" OR "legal" first names
- you can easily print name cards for meetings using various combinations
- you can get all with the same family name in one query
mathetes So sorry for the late response, I don't work on this too often. I am attaching a mockup with fake names here.
Why do you not use the ID for this purpose. That would be FAR more effective long term. People do have similar, even identical, names. It's why corporate payrolls (among other systems) use unique IDs, and you have that field in there.
ANother approach--there are always multiple ways in Excel to accomplish desired goals--is to adopt what i just did: don't ask your user to enter names by typing them into the Daily Attendance. Use the list of official names (or change it to preferred) as the source for a drop down (also called Data Validation). That will be quicker and your existing formula works just fine.
I don't know the process by which you get info in the "Daily Attendance" sheet to begin with (nor do I understand its layout--why the separate sections??--but I suggest you think about doing it slightly differently.
This system is being used to confirm certain people who visit our establishment who are on our records already. They don't know that there is an ID attached to them so they don't have that information when we put their information into Daily Attendance. Therefore, it is not an option to use IDs.
There is also the possibility that multiple names will be entered into one cell, (hence why I also implemented the wildcard feature for XLOOKUP and I probably should have mentioned that earlier). So as wonderful as that dropdown feature would be, I don't think it would work with those (rare, but still present) cases.
Nevertheless, I did manage a solution!
=XLOOKUP("*" & B6 & "*" & IF(D6<>"",D6,C6) & "*",'Daily Attendance'!F$1:F$45,'Daily Attendance'!C$1:C$45,"No Visit",2)Where B6 is Last Name, C6 is First Name, and D6 is Preferred Name.
The IF(D6<>"",D6,C6) returns the preferred name if there is one, otherwise it uses the first name and then searches for that combination using XLOOKUP. Since Preferred Name is always empty if they didn't provide one, this works well for our case. The only scenario that this doesn't cover is if the person provides their actual name instead of preferred at check-in, but so far I have found that to not be the case.Just wanted to respond to this one thing that you wrote: There is also the possibility that multiple names will be entered into one cell, (hence why I also implemented the wildcard feature for XLOOKUP and I probably should have mentioned that earlier). So as wonderful as that dropdown feature would be, I don't think it would work with those (rare, but still present) cases.
If you use the drop-down, drawing it from the list of people who are on your records already (your point earlier), then there can't be multiple names on one cell--which is a bad practice to begin with--and it's easy to just enter each name in its own row.