Related Posts
Rising Star
Conversation Starter
Reasons not to join HCL ??
Was scheduled for an interview today for a Director pos. early morning I get a Reschedule request to next Wednesday but I cannot make it due to personal commitment .
Called recuit mgr.to let him know and he tells me the director is very busy as she’s the IT. Dir for the whole org.politely asked him to send her availability other than next Haven’t received anything since then . Wondering if such co. Is worth pursuing that doesn’t value others time and that too a replacement pos for the same busy role 🥹
Pro
Additional Posts in Excel Genius
Visual Storyteller
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
Just use xlookup instead. If I have staff trying to do vlookups and hlookups, I send it back. You need dynamic formulas that can roll and the old lookups do not follow when you make cell updates. Index match is also easy to learn if you don’t have a version of excel that can use xlookup.
Great advice I’ll be using that from now on
Figure out xlookup like the guy above me said it’s a much easier formula to use
Thanks!
You'd still have the same problem with xlookups.
What you need is data validation, what lookup you use is usually not important at all. Xlookup is the easiest and most flexible but it's not a dealbreaker. If it's a quick lookup then use vlookup if the data source won't change.
Future proofing is nice but nobody expects all formulas to still work when you change the data source format. Even xlookups will break depending on what happens.
Your main problem is that you're using free text against presumably more free text. Use data validation where you can to funnel the answers into definitive responses.
Use dropdowns to control answers and create a helper column for free text as a backup to catch anything else. Just means using a IFS() to target the helper column if it's being used.
The helper column is also a good way to quickly improve your results. If users are having to enter a value not available, add it to reference table/column.
Give users the option and they will spell things however they want.
For example, how many hours did you work on Monday?: "10", "10 ", " 10", "'10" , "Ten", "Ten Hours", "10 Hours", etc are all potential human responses. And I chose 10 specifically because you can have 10 as a number or text, the format also matters with lookups.
You can also add data validation rules. So for the same question, they can't answer more than 24, so it stops 10 becoming 100 accidentally. But realistically nobody is working more than 15 hours or negative hours so you can refine your answer more, this will also keep their answer to a number and prevent text unless they paste over data validation.
Your problem doesn't go away with trim. It just works around the issue but then you have to work around it again if you need to use that column. Solve the problem at source.