vlookup Question about descending numbers

**slx** · 11-12-2007, 03:14 PM

i'm using a workbook to hold my data, and its entered in the order of which it receives data, so basically each new entry is added to the top of the worksheet and the first column has the ID transaction which is higher then the ones previous to it. When I use Vlookup to search the entries. I can't seem to use vlookup to search for my oldest entries. Basically ID numbers under 100 aren't being picked up anymore unless i resort my whole data sheet in ascending order. Is there anyway i can have vlookup work without having to change the order of the IDs from the way they are being entered? or is there a different command i can use?

**Leith Ross** · 11-12-2007, 03:29 PM

Hello Slx,

The formula can lookup in ascending, or unsorted orders, but not descending. This explanation should help...

Syntax

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.
The values in the first column of table_array can be text, numbers, or logical values.
Uppercase and lowercase text are equivalent.

Sincerely,
Leith Ross

**Ron Coderre** · 11-12-2007, 03:32 PM

This would have been a good thread to post your formula into (hint, hint)

Absent that...
Try something like this:

=VLOOKUP(A1,B1:D10,2,FALSE)

I suspect your issue lies with the 4th argument of your VLOOKUP functions. It's either missing or set to TRUE (or 1). For exact matches, it needs to be set to FALSE or 0.

See the Excel Help on VLOOKUP for more information.

Does that help?

**ChemistB** · 11-12-2007, 03:41 PM

If you are looking for specific values, you need your VLOOKUP to end with False. It would look like this;

Please Login or Register  to view this content.

ChemistB

**slx** · 11-12-2007, 03:44 PM

This is what i am using for my formula

=IF(ISNA(VLOOKUP($A$6,'[Client ID Masterlist.xls]CLIENT MASTERLIST'!$1:$65536,FALSE))=TRUE,"",IF(VLOOKUP($A$6,'[Client ID Masterlist.xls]CLIENT MASTERLIST'!1:65536,7,FALSE)="N/A","N/A",IF(VLOOKUP($A$6,'[Client ID Masterlist.xls]CLIENT MASTERLIST'!1:65536,2,FALSE)=0,"",VLOOKUP($A$6,'[Client ID Masterlist.xls]CLIENT MASTERLIST'!1:65536,2,FALSE)&", "&VLOOKUP($A$6,'[Client ID Masterlist.xls]CLIENT MASTERLIST'!1:65536,3,FALSE))))

Its a bit messy, but the core formula is
VLOOKUP($A$6,'[Client ID Masterlist.xls]CLIENT MASTERLIST'!1:65536,2,FALSE)

In terms of descending order, it works up until i get to under 100. IDs 1-99 cant seem to be found.

Thanks for taking your time in lookin into this

**Ron Coderre** · 11-12-2007, 03:53 PM

Originally Posted by slx

In terms of descending order, it works up until i get to under 100. IDs 1-99 cant seem to be found.

Hmmmm....here's a thought:

Check numbers 1-99. Are they really numbers? or are they text?
If those cells are formatted as "text" or contain text (eg '099) and the VLOOKUP is searching for numbers, those text values won't be recognized.

Does that help?

**slx** · 11-12-2007, 04:46 PM

all the numbers in the first colum, my ID column are numbers. Its just that when i put them in descending order, vlookup can't seem to figure out where the IDs 1-99 are, but when its in ascending order, Vlookup has no problems at all.

perhaps i should use the index function instead?

Just that vlookup has been workin pretty well for me up until now.

vlookup Question about descending numbers

LinkBack

Thread Tools

Rate This Thread

Display