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