alternative to VLOOKUP

[Thierry]

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!!

 

[macropod]

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

 

[Roger Govier]

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.