| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help with lookup (vlookup) | Excel Worksheet Functions | |||
| Help with Lookup(), VLookup | Excel Worksheet Functions | |||
| LOOKUP or VLOOKUP | Excel Worksheet Functions | |||
| Lookup without VLOOKUP? | New Users to Excel | |||
| LOOKUP or VLOOKUP | Excel Worksheet Functions | |||
Hybrid Mode
