alternative to VLOOKUP
 

Hi,

What I am looking for is an alternative for the function VLOOKUP.
I want to to exactly the operation this function performs, only I do
not have the option to sort the data in my array.

I am trying the following:

Sheet 2; cel A1:

= 'Sheet 1'!A14

Sheet 2; cel B1:

=INDIRECT("'Sheet 1'!C"&ROW(B3))

Condition: The cel B1 in sheet 2 has to be a reference to Sheet 2 cel
A1.

My problem is that I cannot get the rownumber from Sheet 1 (= 14 in my
example).

Thanks for any help!!

alternative to VLOOKUP
 

Hi Thierry,

If you're looking for exact matches, you can use an INDEX and MATCH field
combination for this, in the form of:
=INDEX(ResultRange,(MATCH(QueryValue,QueryRange,0) )
No need for sorting. If no match is found, you get #NA!.

By changing the 'MatchType' (the '0' above) to '1' or '-1', as appropriate,
the function will also return near matches from a sorted range. See the help
file for more details.

Cheers

--
macropod
[MVP - Microsoft Word]


"Thierry" wrote in message
ups.com...
Hi,

What I am looking for is an alternative for the function VLOOKUP.
I want to to exactly the operation this function performs, only I do
not have the option to sort the data in my array.

I am trying the following:

Sheet 2; cel A1:

= 'Sheet 1'!A14

Sheet 2; cel B1:

=INDIRECT("'Sheet 1'!C"&ROW(B3))

Condition: The cel B1 in sheet 2 has to be a reference to Sheet 2 cel
A1.

My problem is that I cannot get the rownumber from Sheet 1 (= 14 in my
example).

Thanks for any help!!

alternative to VLOOKUP
 

Hi Thierry

You do not have to have the data sorted to use VLOOKUP().
Just include the optional 4th parameter of either FALSE or 0 to deal
with unsorted tables.

=VLOOKUP(value,mytable,offset,0)
substitute entries for value, mytable and offset to suit your needs.

--
Regards

Roger Govier


"Thierry" wrote in message
ups.com...
Hi,

What I am looking for is an alternative for the function VLOOKUP.
I want to to exactly the operation this function performs, only I do
not have the option to sort the data in my array.

I am trying the following:

Sheet 2; cel A1:

= 'Sheet 1'!A14

Sheet 2; cel B1:

=INDIRECT("'Sheet 1'!C"&ROW(B3))

Condition: The cel B1 in sheet 2 has to be a reference to Sheet 2 cel
A1.

My problem is that I cannot get the rownumber from Sheet 1 (= 14 in my
example).

Thanks for any help!!