Re: VLOOKUP - Need Reverse?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Polarbear (Polarbear_at_discussions.microsoft.com)
Date: 06/30/04 

Date: Tue, 29 Jun 2004 22:54:01 -0700

Have you tried vlookup using a negative index number, it seems to me that i had done that before and it workd, the negative number was the count to the left of your search column

"RagDyer" wrote:

> A common approach to return data from any position in relation to the lookup
> column or row is the combination of INDEX() and MATCH().
>
> The index column contains the data that you want returned, while the match
> column contains the values that you are looking to match up.
> The value to match is the first argument in the Match function.
>
> With data to be returned listed in column A, and values to match up in
> column D, and value to lookup in E1, here's an example:
>
> =INDEX(A1:A100,MATCH(E1,D1:D100,0))
>
> Since you're familiar with Vlookup, this Index aqnd Match combination can
> also be utilized in a very similar fashion, using specific column numbers
> within the formula to return data.
>
> Taking your scenario:
>
> Data list is A1 to E100
> Your key column is right in the middle, column C.
> The value to find is entered into F1:
>
> =INDEX(A1:E100,MATCH(F1,C1:C100,0),1)
> The final 1 on the formula returns from column A
> Change it to anything up to a 5 (only 5 columns were indexed), to return
> data from any of the columns, *including* the column you're using as the
> key, column C.
>
> This version on Index and Match is almost exactly the same as Vlookup,
> without the restriction of the placement of the key column.
> --
>
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
> "Terry" <anonymous@discussions.microsoft.com> wrote in message
> news:2341501c45e2b$524d7870$a501280a@phx.gbl...
> The VLookup function searches for value in the left-most
> column of table_array and returns the value in the same
> row based on the index_number.
>
> Question is>>>
>
> How do I lookup & return a value contained in a cell
> further to the left of the table_array?
>
>
> I have a Core Spread*** where I use VLOOKUP to return
> Data from another ***. Problem is, someone designed the
> spread*** with the Primary Lookup reference key in the
> middle. I have no problem getting data to the right of
> the primary reference column..but cannot for the life of
> me get data from the left.
>
> I have an example if required..any help is much
> appreciated...
>
> TerryH
>
>
>
>
>
>
>
>
>
>
>

Quantcast