Idle Banter For non SV and non bike related chat (and the odd bit of humour - but if any post isn't suitable it'll get deleted real quick). There's also a "U" rating so please respect this. Newbies can also say "hello" here too. |
|
Thread Tools |
30-09-14, 10:14 PM | #1 |
Member
Mega Poster
Join Date: Aug 2003
Location: Back in Bolton
Posts: 1,008
|
Excel Help = Vlookup
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!
__________________
Had an SV or three. Street triple R - gone but not forgotten. Now trying the lunacy that is KTM with a Superduke GT. for the pillion capability of course. |
01-10-14, 09:39 AM | #2 |
Guest
Posts: n/a
|
Re: Excel Help = Vlookup
I think what you are trying to do is:
=LEFT(VLOOKUP(A2,'[Test Lookup.xlsx]Sheet1'!$A:$C,2,FALSE), 4) |
01-10-14, 09:45 AM | #3 |
Guest
Posts: n/a
|
Re: Excel Help = Vlookup
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-co...-with-vlookups Last edited by rigor; 01-10-14 at 09:48 AM. |
01-10-14, 10:54 AM | #4 |
Member
Mega Poster
Join Date: Aug 2005
Location: Fleet, Hampshire
Posts: 2,448
|
Re: Excel Help = Vlookup
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.
__________________
Don't want to be the quickest; don't want to be the best; just want to be the one having the most fun. XL125Varadero -> Curvy SV650N-Y -> SV1000N-K3 -> Multistrada 1200s Twin-tastic stuff. Minister for Sustainability Aliquid prudentissimus delectabiles et intelligentes in adamasset lingua. |
01-10-14, 01:38 PM | #5 | |
Member
Mega Poster
Join Date: Aug 2003
Location: Back in Bolton
Posts: 1,008
|
Re: Excel Help = Vlookup
Quote:
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
__________________
Had an SV or three. Street triple R - gone but not forgotten. Now trying the lunacy that is KTM with a Superduke GT. for the pillion capability of course. |
|
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel experts willing to help....? | MisterTommyH | Idle Banter | 8 | 19-05-14 10:03 AM |
OMO Excel Help | L3nny | Idle Banter | 3 | 04-02-13 02:47 PM |
Excel Help Please? | -Ralph- | Idle Banter | 15 | 05-07-11 08:13 PM |
Help with excel please | Stingo | Idle Banter | 10 | 19-05-11 02:26 PM |
Excel | Graciepants | Idle Banter | 15 | 31-03-09 10:52 PM |