Solved: VLOOKUP Problem

[ukdane]

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]

Should be comma's

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

[jazznaura]

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

[ukdane]

Not in Danish version.

[Aussiebear]

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]

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]

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]

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]

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]

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.