+ Reply to Thread
Results 1 to 11 of 11

Vlookup alternative

  1. 06-10-2013, 04:36 PM #1
    JohnDoh
    JohnDoh is offline
    Forum Contributor
    Join Date
    03-04-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    130

    Vlookup alternative

    Hi Gents,

    Just a quick one, i was looking over a spreadsheet i have been working on and wanted to ask some advice.

    In the attached, the formula in columns B & I of the Quotation sheet seems to work ok, I just wonder if its the most efficient way to do what its doing, which is basically looking up the cost and labor time based on the text in column A.

    Any suggestions? The reason i ask is that in another spreadsheet (a much larger one) it does not work for some parts of the data base.

    Thanks in advance.

    Adam
    Attached Files Attached Files
    Last edited by JohnDoh; 06-10-2013 at 04:42 PM.
    Reply With QuoteRegister To Reply
  2. 06-10-2013, 04:42 PM #2
    JohnDoh
    JohnDoh is offline
    Forum Contributor
    Join Date
    03-04-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    130

    Re: Vlookup alternative

    Sorry wrong attachment.

    Attachment 242125
    Reply With QuoteRegister To Reply
  3. 06-10-2013, 04:42 PM #3
    Jonmo1
    Jonmo1 is offline
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Vlookup alternative

    A pretty common alternative to Vlookup is Index/Match.
    although it appears longer and more complicated, generally speaking it is more efficient and much more flexible.

    Syntax is
    =INDEX(ReturnValueColumn,MATCH(LookupValue,FindColumn,0))

    Example, with this Basic Vlookup
    =VLOOKUP(A1,'Other Sheet'!A:B,2,FALSE)

    Equivelent using Index Match would be
    =INDEX('Other Sheet'!B:B,MATCH(A1,'Other Sheet'!A:A,0))


    Hope that helps.
    Reply With QuoteRegister To Reply
  4. 06-10-2013, 05:06 PM #4
    JohnDoh
    JohnDoh is offline
    Forum Contributor
    Join Date
    03-04-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    130

    Re: Vlookup alternative

    Not sure i have done it right.

    is it not as simple as,

    Please Login or Register  to view this content.
    Or have i just made a fundamental error.
    Reply With QuoteRegister To Reply
  5. 06-10-2013, 05:14 PM #5
    Jonmo1
    Jonmo1 is offline
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Vlookup alternative

    Depends on what the original formula was..
    That doesn't seem to match the data in the book you posted..

    That would be the equivelent of
    =VLOOKUP(A1,Products!C3:D433,2,FALSE)
    Reply With QuoteRegister To Reply
  6. 06-10-2013, 05:26 PM #6
    JohnDoh
    JohnDoh is offline
    Forum Contributor
    Join Date
    03-04-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    130

    Re: Vlookup alternative

    Think I have it working.

    If the original was.

    Please Login or Register  to view this content.
    Then it should be.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 06-10-2013, 05:34 PM #7
    Pete_UK
    Pete_UK is online now
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,882

    Re: Vlookup alternative

    That's not directly equivalent because you are not trapping the error if the lookup value doesn't exist. Also, you haven't used the $ symbol for the ranges, so If you copy it down the ranges will change. In Excel 2007 there is a more efficient way of trapping errors, using IFERROR, like this:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  8. 06-10-2013, 05:47 PM #8
    JohnDoh
    JohnDoh is offline
    Forum Contributor
    Join Date
    03-04-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    130

    Re: Vlookup alternative

    Yes, your right of course.

    One last question, is there any reason why that formula would not find a value made up from several other values.

    For example the database is a list of plumbing fittings and the single fittings work fine, but where I have made an assembly up from adding together several items the (ie B1+B+B3 etc) it does not show the value in the table.

    I have used the ~ symbol in the description, this does not have some unforeseen effect im not aware of does it?

    Edit: The ~ symbol was the problem, Thanks all for your help.
    Last edited by JohnDoh; 06-10-2013 at 06:01 PM.
    Reply With QuoteRegister To Reply
  9. 06-10-2013, 06:03 PM #9
    Pete_UK
    Pete_UK is online now
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,882

    Re: Vlookup alternative

    MATCH (and VLOOKUP) will stop looking for items when it finds the first that matches the lookup value. However, both can be used with wildcard characters, so you could to this:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    or this with VLOOKUP:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    to find the first value which contains A12 within it, i.e. match any number of characters before and/or after.

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  10. 06-10-2013, 06:18 PM #10
    JohnDoh
    JohnDoh is offline
    Forum Contributor
    Join Date
    03-04-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    130

    Re: Vlookup alternative

    Thats great thanks,

    But would I be right in thinking that this would not be necessary as all the items in the database have a unique description ?
    Reply With QuoteRegister To Reply
  11. 06-10-2013, 07:10 PM #11
    Pete_UK
    Pete_UK is online now
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,882

    Re: Vlookup alternative

    Yes, that's correct.

    However, there are times when you might have more than one entry of the same unique item, and in this case MATCH and VLOOKUP will only be able to find the first occurrence. (There are other techniques to help you get the other occurrences).

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1