PDA

View Full Version : Solved: VLOOKUP Problem



ukdane
03-05-2010, 08:32 AM
Is there any reason why the VLOOKUP returns N/A error for any values that exist after row 3593?

The following formula

=VLOOKUP($D$18;'C:\\myworksheet.xls]Sheet1'!$C:$D;2;FALSE)

Is returning NA for every value after row 3593.

Thanks

jazznaura
03-05-2010, 08:46 AM
Should be comma's

=VLOOKUP($D$18,'C:\[myworksheet.xls]Sheet1'!$C:$D,2,FALSE)

jazznaura
03-05-2010, 08:47 AM
=VLOOKUP($D$18,'C:\[myworksheet.xls]Sheet1'!$C:$D,2,FALSE)

ukdane
03-05-2010, 09:57 AM
Not in Danish version.

Aussiebear
03-06-2010, 02:56 AM
From Excel Help

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.

ukdane
03-09-2010, 12:30 AM
Unfortunately, in order to test that the system works, I have copy/pasted the result I want to find- that way I know that an exact match exists.
However it's still producing #N/A results.

On another matter, I've tried another vlookup where the array to lookup in is A:B I know the result can be found in column B, and I need it to return the column A result. Can this be done, I've tried using -1 but to no avail.
I can't swap the columns around, as the searching excel file is external, and not under my control.
Ideas?

Thanks

arkusM
03-11-2010, 08:01 AM
Like Aussiebear says vlookup looks for an exact match so if you know the data is in the source array then there is something else that is causing the vlookup to fail, you need to isolate that issue.
Sometimes there is crappy bits in the data, like extra spaces or a ' in the data. This always screws up the vlookup and is not obvious when you look at the data or copy and paste the data because Excel does not display the '.

If it is a spacing issue you can try

=VLOOKUP(Trim($D$18);'C:\\myworksheet.xls]Sheet1'!$C:$D;2;FALSE)

However if it is a ' issue then it is trickier. There is a way to get rid of the extra ', but I don't recall off the top of my head try googleing it.

mdmackillop
03-11-2010, 11:00 AM
On another matter, I've tried another vlookup where the array to lookup in is A:B I know the result can be found in column B, and I need it to return the column A result

=INDEX(A:A,MATCH(D1,B:B,0))

Aussiebear
03-11-2010, 02:16 PM
Vlookup is unable to return a value from a column to the left of the initial lookup column, so you will need to use MD's alternative

arkusM
03-12-2010, 07:19 AM
I know the result can be found in column B, and I need it to return the column A result.


Good Catch MD, I guess that is why you are the Wizard.