lookup or vlookup
 

Hi
I have this worksheet

25 apple
15 banana
30 cucumber
5 tomato

If I use vlookup or lookup function, the data should be sorted, the
default of these functions is ascending or descending, but I do not
want to sort the data, any help?
Thanks in advance
Jam

lookup or vlookup
 

vlookup should be fine. Let's assume you wanted to return cucumber you could
do:

=vlookup(30,a1:b4,2,)

Should search for 30 in the array of a1:b4 (which is all of your data), go
to the second column and return the data in the same row as 30. It should
give you cucumber. Only specific sorts of vlookup require the data to be
sorted.

Rob

" wrote:

Hi
I have this worksheet

25 apple
15 banana
30 cucumber
5 tomato

If I use vlookup or lookup function, the data should be sorted, the
default of these functions is ascending or descending, but I do not
want to sort the data, any help?
Thanks in advance
Jam

lookup or vlookup
 

Think your
=vlookup(30,a1:b4,2,)

should read as:
=vlookup(30,a1:b4,2,0)
where an exact match is specified via the zero* as the 4th param
*or FALSE

As for your comment:
Only specific sorts of vlookup require the data to be sorted.

It should be the other way around, specific match in vlookup (as above)
doesn't require the data to be sorted
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---
"exoticdisease" wrote in message
...
vlookup should be fine. Let's assume you wanted to return cucumber you
could
do:

=vlookup(30,a1:b4,2,)

Should search for 30 in the array of a1:b4 (which is all of your data), go
to the second column and return the data in the same row as 30. It should
give you cucumber. Only specific sorts of vlookup require the data to be
sorted.

Rob

lookup or vlookup
 

"exoticdisease" has a right syntax. The last comma after the third param is
automatic default to "0" or "FALSE" if the fourth param is omitted.


"Max" wrote:

Think your
=vlookup(30,a1:b4,2,)

should read as:
=vlookup(30,a1:b4,2,0)
where an exact match is specified via the zero* as the 4th param
*or FALSE

As for your comment:
Only specific sorts of vlookup require the data to be sorted.

It should be the other way around, specific match in vlookup (as above)
doesn't require the data to be sorted
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---
"exoticdisease" wrote in message
...
vlookup should be fine. Let's assume you wanted to return cucumber you
could
do:

=vlookup(30,a1:b4,2,)

Should search for 30 in the array of a1:b4 (which is all of your data), go
to the second column and return the data in the same row as 30. It should
give you cucumber. Only specific sorts of vlookup require the data to be
sorted.

Rob

lookup or vlookup
 

No it defaults to 1 or TRUE if omitted

--


Regards,


Peo Sjoblom

"Teethless mama" wrote in message
...
"exoticdisease" has a right syntax. The last comma after the third param
is
automatic default to "0" or "FALSE" if the fourth param is omitted.


"Max" wrote:

Think your
=vlookup(30,a1:b4,2,)

should read as:
=vlookup(30,a1:b4,2,0)
where an exact match is specified via the zero* as the 4th param
*or FALSE

As for your comment:
Only specific sorts of vlookup require the data to be sorted.

It should be the other way around, specific match in vlookup (as above)
doesn't require the data to be sorted
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---
"exoticdisease" wrote in
message
...
vlookup should be fine. Let's assume you wanted to return cucumber you
could
do:

=vlookup(30,a1:b4,2,)

Should search for 30 in the array of a1:b4 (which is all of your data),
go
to the second column and return the data in the same row as 30. It
should
give you cucumber. Only specific sorts of vlookup require the data to
be
sorted.

Rob

lookup or vlookup
 

Without the last comma in the third param then it defaults to 1 or TRUE, else
it defaults to 0 or FALSE.


"Peo Sjoblom" wrote:

No it defaults to 1 or TRUE if omitted

--


Regards,


Peo Sjoblom

"Teethless mama" wrote in message
...
"exoticdisease" has a right syntax. The last comma after the third param
is
automatic default to "0" or "FALSE" if the fourth param is omitted.


"Max" wrote:

Think your
=vlookup(30,a1:b4,2,)

should read as:
=vlookup(30,a1:b4,2,0)
where an exact match is specified via the zero* as the 4th param
*or FALSE

As for your comment:
Only specific sorts of vlookup require the data to be sorted.

It should be the other way around, specific match in vlookup (as above)
doesn't require the data to be sorted
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---
"exoticdisease" wrote in
message
...
vlookup should be fine. Let's assume you wanted to return cucumber you
could
do:

=vlookup(30,a1:b4,2,)

Should search for 30 in the array of a1:b4 (which is all of your data),
go
to the second column and return the data in the same row as 30. It
should
give you cucumber. Only specific sorts of vlookup require the data to
be
sorted.

Rob

lookup or vlookup
 

I didn't notice that it was a comma, nevertheless it is hard to call it the
right syntax since it is undocumented and probably somewhat of a bug as
well.

--


Regards,


Peo Sjoblom

"Teethless mama" wrote in message
...
Without the last comma in the third param then it defaults to 1 or TRUE,
else
it defaults to 0 or FALSE.


"Peo Sjoblom" wrote:

No it defaults to 1 or TRUE if omitted

--


Regards,


Peo Sjoblom

"Teethless mama" wrote in
message
...
"exoticdisease" has a right syntax. The last comma after the third
param
is
automatic default to "0" or "FALSE" if the fourth param is omitted.


"Max" wrote:

Think your
=vlookup(30,a1:b4,2,)

should read as:
=vlookup(30,a1:b4,2,0)
where an exact match is specified via the zero* as the 4th param
*or FALSE

As for your comment:
Only specific sorts of vlookup require the data to be sorted.

It should be the other way around, specific match in vlookup (as
above)
doesn't require the data to be sorted
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,200 Files:354 Subscribers:53
xdemechanik
---
"exoticdisease" wrote in
message
...
vlookup should be fine. Let's assume you wanted to return cucumber
you
could
do:

=vlookup(30,a1:b4,2,)

Should search for 30 in the array of a1:b4 (which is all of your
data),
go
to the second column and return the data in the same row as 30. It
should
give you cucumber. Only specific sorts of vlookup require the data
to
be
sorted.

Rob

lookup or vlookup
 

TM, thanks

But on the basis of exoticdisease's clarifying comment:
Only specific sorts of vlookup require the data to be sorted.

I'm not sure that s/he understands what is really happening
with the type of vlookup construct that s/he uses
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,300 Files:356 Subscribers:53
xdemechanik
---