Forum Discussion
Help with XLOOKUP formula to find one cell and either cell b or cell c
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.