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?

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.