Forum Discussion
Help with XLOOKUP formula to find one cell and either cell b or cell c
Hello! I am using the latest version of Excel on Windows 10.
I am trying to use XLOOKUP to find a first and last name on one sheet and if it is found, record the date and time. However, my manager now wants to search by "preferred" first name, which is a different cell.
Current formula:
=XLOOKUP("*" & B10 & "*",'Daily Attendance'!F$1:F$104,'Daily Attendance'!C$1:C$104,"No Visit",2)
Current Table Format:
| Name | Preferred Name | Visit Time |
| Doe, John | Johnny | No Visit |
At this time, the formula works to find "Doe, John" and change the Visit Time cell if the name is found. However, I am not sure how to go about checking IF last name AND (OR first name or preferred name).
I know things get complicated with having the first and last name in the same cell, so if necessary they can be separated. However, if there is a solution without doing that it would be preferred.
Latest formula:
=XLOOKUP("*" & I10 & "*" & OR(J10, K10) & "*",'Daily Attendance'!F$1:F$104,'Daily Attendance'!C$1:C$104,"No Visit",2)
Separated Table Format:
| Last Name | First Name | Preferred Name | Visit Time |
| Doe | John | Johnny | No Visit |
However, this latest formula gives me a #VALUE! error. What adjustments can I make? Is this possible with XLOOKUP?
The Visit Time cell should be changed if John Doe OR Johnny Doe is found.
Thank you in advance! I'm happy to provide more information if necessary.
5 Replies
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.
