+ Reply to Thread
Results 1 to 2 of 2

Alternative to Vlookup--Where there is more than one lookup value

  1. #1
    Registered User
    Join Date
    07-09-2012
    Location
    Kansas City, MO
    MS-Off Ver
    Excel 2010
    Posts
    2

    Alternative to Vlookup--Where there is more than one lookup value

    Good morning,

    I've become quite reliant on Vlookups in a new position I just took. However, I've run into an issue on my latest attempt at something new.

    Ordinarly when I get a new data set I assign a UID, or universal identifier to earch row/line item. When I run batch rates I normally vlookup off of this UID. In all previous instances when we run a batch rate off of our client's data we want the vlookup to return the least cost option (our batch rater returns them in order of least cost to most cost--so normally the return column is constant and the vlookup works great). However, I'm doing an analysis where I need to bring in a base line of the clients current "preferred" carrier which is not ususally the least cost.

    So I need a formula that will first take me to a specific row based off of the UID, then find the preferred carrier SCAC (Name) within that row, and then return the cell 2 places to the right.......

    Any ideas?

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: Alternative to Vlookup--Where there is more than one lookup value

    Generally speaking, when you want to do a LOOKUP with more flexability, you migrate from VLOOKUP() to INDEX(MATCH()).

    INDEX(array, row_num)
    This will find read down an array until the designated row number. There are other ways to use INDEX (on a matrix, reference form) but we are not concerned with them right now.

    MATCH(value, array, type)
    This will take a value and return the position in the array. Type is -1, 0, or 1; you will probably want to use 0, which will return only an exact match, not the next closest value (as -1 and 1 would).

    So MATCH finds the position of a reference, and INDEX can take that position to return the same position in a different row, like this:

    Please Login or Register  to view this content.
    You can next the MATCH into the Index to reduce the number of cells you occupy but this set-up would require less flops.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1