Re: VLOOKUP - Need Reverse?

From: RagDyer (ragdyer_at_cutoutmsn.com)
Date: 06/30/04 

Date: Tue, 29 Jun 2004 19:03:47 -0700

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