Excel Help = Vlookup

[BoltonSte]

Calling the Excel Gurus


I have been using Vlookup for a while at work, along with the value left function;
=VLOOKUP(LEFT(C7,9),Parameters!B4:R200,3)


I now want to do something similar but using the table from another workbook;


In the simple format this works fine
=VLOOKUP(A2,'[Test Lookup.xlsx]Sheet1'!$A:$C,2,FALSE)


But when I try and combine the vlookup from a different workbook and the value left function it goes a bit wrong;
=VLOOKUP(LEFT(A2,4),'[Test Lookup.xlsx]Sheet1'!$A:$C,3,FALSE)


All I get is #N/A in the cell, I know I'm doing something wrong with the Value left but no idea what.


Trawling the web isn't working and our excel and IT resource is limited to say the least.


Any help appreciated.


Yours
Buggered if I know!

[SvNewbie]

I think what you are trying to do is:

=LEFT(VLOOKUP(A2,'[Test Lookup.xlsx]Sheet1'!$A:$C,2,FALSE), 4)

[rigor]

Quick look online leads me to think it may be that the format of the target cell changes somewhere in the range, is it all formatted as text in the other workbook?

Or

http://howtovlookupinexcel.com/10-common-problems-with-vlookups

[Mark_h]

I usually break it all down when I get stuck.

Just get it to return the left4 from the other sheet as a diagnostic and make sure that works and then do a v-lookup once you know that the left bit works. I'd also consider putting the LEFT(A2,4) bit in it's own brackets too.

I find mrexcel.com has most answers to most things without resorting to VBA.

Also not sure if you may want to put the Y-coordinate into the descriptor for the lookup table. I've never tried it with just columns.

[BoltonSte]

I think what you are trying to do is:

=LEFT(VLOOKUP(A2,'[Test Lookup.xlsx]Sheet1'!$A:$C,2,FALSE), 4)



The winner!


This seems to work, it threw me as the left and associated cell works when after the vlookup if it's the same workbook.


Ta All