• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup

HP_81

New Member
Hi Excel Champs,

I have another one on Vlookup please.
How can i exclude the -1/2,2/2 and so on from sheet1 when looking up from Criteria tab.

I have attached a sample file..Thank you
 

Attachments

  • Sample (2).xlsx
    13.4 KB · Views: 1
Column B rigidly chops of the last 4 characters and looks that up. Not good if there aren't exactly 4 characters to remove at the end.
Column C chops everything including and after the last hyphen.
Column D is a more up to date version of column c with some of the newer functions.
Take your choice.
 

Attachments

  • Chandoo51134Sample (2).xlsx
    17.9 KB · Views: 4
Column B rigidly chops of the last 4 characters and looks that up. Not good if there aren't exactly 4 characters to remove at the end.
Column C chops everything including and after the last hyphen.
Column D is a more up to date version of column c with some of the newer functions.
Take your choice.
1] The shortest formula of p45cal is:

=VLOOKUP(LEFT(A1,LEN(A1)-4),Criteria!A:A,1,0)

But it contents 3 functions.

2] I think this formula is the fastest.

=LOOKUP(1,-FIND(Criteria!A$1:A$11,Sheet1!A1),Criteria!A$1:A$11)

The above using 2 functions and limited Lookup range and Lookup result.

LOOKUP and VLOOKUP and one of the Lookup family.

And

LOOKUP is the fastest function because it used binary search method, while the others used linear search way.

Regards
Bosco
 
Last edited:
Excellent...Thanks a lot p45cal.

Thank you bosco_yip..I was not aware of the lookup function.

Really appreciate it.
 
Back
Top