You can use MATCH but keep in mind that MATCH returns the *relative*
position of the lookup_value in the lookup_array. If you want the absolute
address you can still use MATCH but then you have to calculate for any
offset.

Biff

"Ronald Dodge" wrote in message
...
Me personally, I generally use the MATCH function instead of the LOOKUP
function for this sort of thing. The MATCH function has the following
arguments:

Lookup_Value (Same as syntax 1 of the LOOKUP function)

Lookup_Vector (Same as syntax 1 of the LOOKUP function)

Match_Type (-1 = smallest value that is greater than or equal to the
Lookup_Value [numbers are in descending order, but returns #N/A! error if
no number is larger or equal to the Lookup_Value], OR 1 = largest value
that is smaller than or equal to the Lookup_Value [numbers are in
ascending order, but returns #N/A! error if no number is smaller or equal
to the Lookup_Value], OR 0 = first item to equal to the Lookup_Value [no
number order necessary, but returns "#N/A!" error if no match is found])

In your case, you would use the value of 1 for the Match_Type argument
with T. Valko's example assuming you use the same lookup value as T. Valko
explained.

The MATCH function automatically returns the Nth number of the item found
within the list as opposed to the LOOKUP function requiring a
Result_Vector, which that also becomes handy in other situations.

I use MATCH, ISERROR (to trap the #N/A error within any of the lookup
functions, which requires the use of the IF function), ADDRESS and
INDIRECT quite a bit in these types of formulas. Sometimes, I'm even
using at least one of the following: ROW, COLUMN, CELL (to get the file
name and/or sheet name), SEARCH, FIND, LEFT, RIGHT, or MID
--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

wrote in message
ps.com...
Very nice. Thank you for taking the time to explain. I learned a
couple new tricks!

So, the function returns the value from the result_vector that
corresponds
to the *last* value in the lookup_vector that is less than the
lookup_value.
The values in the result_vector are the column numbers: COLUMN(A1:H1).

I did not know you could specify COLUMN (or non-ranges) for the
results vector. Or is Excel seeing the COLUMN as a "range"? Can you
also specify an array of numbers for the results vector?

I remember seeing VLOOKUP can be used as an array or vector format,
but now that I'm looking in the Excel help file, I'm only seeing
references to the array format. Where does Microsoft document the
vector format?

Thanks again!