Excel VLOOKUP...

[SenatorKang]

Does anyone know excel?
I have a list, using the VLOOKUP function it gives the value above the one I want... Ie: VLOOKUP(A2,Data!A1:G1036,6), will get what I've written in cell A2, find it in the sheet called DATA, go across to the 6th column - which is all good - THEN goes UP one cell and gives that value! aaaaargh!

Any ideas?

[WhiteD3]

How about setting the range lookup?

range_lookup Optional. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:
If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.
Important If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.

If range_lookup is FALSE, the values in the first column of table_array do not need to be sorted.

If the range_lookup argument is FALSE, VLOOKUP will find only an exact match. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

[SenatorKang]

I tried that :(

But in the end it turns out that in exporting the data from our proggy into Excel spaces were added to the end of the part number to fill up the cell. Like, "E316YB-10" was actually "E316YB-10 ", so adding a bunch of spaces to the end of the p/n when looking it up seems to fix it. Its not a very good solution as the whole point ofthe exercise was to eliminate human error as much as possible... forgettig to put the spaces in and ending up with the wrong info is not very good :(

Is there a way to remove the spaces from the end of the part numbers without having to go through with the backspace key? There's 1036 numbers to do....

[p38arover]

Is the data secret? If not, can you email me the Excel file? I use vlookup in some spreadsheets I've written to calculate wages and taxes.

It works perfectly. I'm happy check yours over.

If you like, delete most of the rows in the data and send me the cut down sheet with just a few rows of data. Falsify the critical stuff like prices, etc.

[WhiteD3]

I tried that :(

But in the end it turns out that in exporting the data from our proggy into Excel spaces were added to the end of the part number to fill up the cell. Like, "E316YB-10" was actually "E316YB-10 ", so adding a bunch of spaces to the end of the p/n when looking it up seems to fix it. Its not a very good solution as the whole point ofthe exercise was to eliminate human error as much as possible... forgettig to put the spaces in and ending up with the wrong info is not very good :(

Is there a way to remove the spaces from the end of the part numbers without having to go through with the backspace key? There's 1036 numbers to do....
Yes, look up the Text functions, in particular "clean", "left" and "right". This sort of thing is always an issue when you import data.

[SenatorKang]

Thanks

No, its not secret :D. Just part numbers, descriptions and stock levels. I'll give the 'clean' thing a go on monday, then if it doesn't work perhaps i will sling it your way?

Thanks

[p38arover]

No worries. In some things Excel doesn't worry about trailing blanks, in others, it does.