Hi All,
I have two columns phone number and fax number. Here I need to find the whichever phone number is there in fax number. Generally I used to do vlookup and find them. Is this same thing can we do in vba? The number of rows i used in this example are 39 rows. It may randomly vary. Please find the attached file.
Thanks
Why not
=IFERROR(VLOOKUP($C2,$B$2:$B$40,1,0),"")
If my records increase. Do this work? Example if i have 1000 records?
1000 is nothing. Unless you have somewhere in excess of 25k records. You are fine with simple VLookup.
If speed becomes issue, use double approximate VLOOKUP to take advantage of binary search.
Formula:
Please Login or Register to view this content.
Note that this requires "Phone Number" column to be sorted ascending (smallest to largest in sort option).
FYI - This will be faster than VBA method on sorted data.
You can read up more in detail about double approximate VLOOKUP in link below.
https://fastexcel.wordpress.com/2012...han-1-vlookup/
Also, I'd either put data range into Excel Table and use structured reference or use Dynamic Named range.
So that you don't have to adjust lookup range when data range changes.
PhNum (Named range)
Formula:Please Login or Register to view this content.
See attached.
Last edited by CK76; 05-23-2018 at 04:24 PM. Reason: Added FYI
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks