VLOOKUP is not returning the value from the correct column index in a named range

[biederboat]

I am successfully using VLOOKUP to find values in a named range located in another workbook. Beyond a certain number of columns, it starts to fall apart. I'm grabbing 3 fields in column index 9, 10 and 12 which work fine but beyond that it either gives an error (#REF!) or returns the value from the correct row but incorrect column index (e.g. index of 14 returns the value in column index 15). I've serached for hidden columns and double-checked my formula and I'm at a loss.

Sounds like INDX-MATCH might solve this but it's simple enough I'd sure like to fix it (or at least understand why it's not working).

Thanks,
BB

[Sam Capricci]

You might have to post a sample so people can look at the formula and the data it is pointing to.

[ELeGault]

You are stepping out of your reference area. Your formula should look like this assuming you are within a 15 cell range starting with A

=VLookup(VALUE,$A:$1:$O$25,14,False)

in a Vlookup, the first column named (A in the example I gave) is 1 and you count up from there. So the max I could go with what I gave is 15, which would return the value in column O. Share your code, I am guessing you are out of boundaries. Boundaries being the range like the A1:O25 in my example.

[biederboat]

Thanks, but I'm 100% certain I'm in the boundary. I'm using a named range that, when I select it directly, goes all the way out to column S, which would have an index of 19 (since the first column of my named ranged is A). I tried using the same exact formula in the target workbook and it returns the correct value. This appears to have something to do with the fact that I'm going outside of my workbook to another workbook.

I did get it working by putting both files in the same directory (originally the Vlookup was on my hard drive and the target on our server). I am not familiar with how to post code (Ssorry!). My formula that is doing the lookup is: =VLOOKUP($C$2,'\\PSSERVER\Quotes\Quote log.xls'!Customer,15,FALSE) This did not change after I moved the file to the server. Again, the only issue was that after column index 12 it wasn't working (but now is).

Not sure there's any sense trying to analyze this further but, if someone knows what the error source was I would be interested in knowing. It was always my intention to move the file to the server; as I sometimes work at home it was just more convenient to keep it on my hard drive.

Thanks,
BBB