Ben has attended:
Excel Advanced course
Vlookup
When using approximations in a Vlookup on a text field, can I get it to return a value ONLY if it is close to the value being searched for (maybe if I input acceptance criteria)?
RE: Vlookup
Hi Ben
Thankyou for your question
I have come up with a formula that returns a lookup result if a cell contains a particular string, e.g the lookup table contains the value Peter and you want it to work for both Peter and Pete
=IF(ISERROR(FIND("Pet",A2)),"",VLOOKUP(A2,$J$2:$K$6,2,FALSE))
The find function looks for a combination of letters within a string, here "pet" within a cell containing the word peter. If the cell doesn't contain the string then the function returns an error. Thus we nest this within an iserror function, itself within an if function. If the if result is false, i.e there is no error then it runs the vlokup, otherwsie it returns an empty string
Note Find is case sensitive
Hope this helps
Regards
Stephen
RE: Vlookup
Hi Stephen,
Thanks for replying.
Your formula relies on a nested value. You could make it relative to another column (e.g. (Left(A2,3)) but this would still require me to define the boundaries of the search. I look at a huge number of records at a time (approx 1000), and I would be looking for a formula that can be dragged down and remains robust.
My Left formula would be OK for "O2 Local" matching to "O2 Contract Local", but would also think that "SAP Delivery BT Telecom" is the same as "SAP Delivery Orange Telecom"
Is there a way of programming a search which is nearly as good as a manual check?
Thanks,
Ben
RE: Vlookup
"Your formula relies on a nested value."
Sorry, I mean "embedded value"
RE: Vlookup
Hi Ben
The instr (instring function) in visual basic would be the best way forward here, although its use would require a good knowledge of VBA as gained from our VBA advanced course.
It would however still require you to be careful with your search string. The example you quote of "SAP Delivery" would indeed not work and you would have to specify your search string more accurately
Regards
Stephen
