Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › [SOLVED] Need an Excel 2010 function, formula, or macro
New Posts  All Forums:Forum Nav:

[SOLVED] Need an Excel 2010 function, formula, or macro - Page 2

post #11 of 16
Thread Starter 
Quote:
Originally Posted by Plan9 View Post

If it were me, I'd change the cell reference to LEFT(cell, 3) to only pick up the first 3 characters and then change FALSE back to TRUE. So your formula would look something like:
=VLOOKUP(LEFT(cell, 3), A1:B3, 2, FALSE)


Personally I don't trust fuzzy matching as there's a theoretical chance it could do an incorrect match (and after a while you'll get complacent thinking the formula works so might not notice an error in the match). By forcing an absolute match then you know that your results will always be correct.

Ok, so I have this;
Code:
=VLOOKUP(D14, Building, 2, TRUE)
// D14 is the cell being referenced for the building designator character.

Would something like this work?
Code:
{=LEFT(D14, 3) & VLOOKUP(D14, Building, 2, TRUE)}
    
CPUMotherboardGraphicsRAM
E8400 DFI LP DK P45-T2RS Sapphire 4850 512MB 2x2GB G.Skill 1066 (5-5-5-15-2T 1.9V) 
Hard DriveOptical DriveOSMonitor
820GB Total LG Super DVD Burner SATA VII Enterprise x64 Gateway FHD2401 
PowerCaseMouseMouse Pad
Corsair VX550W NZXT Source 210 White Logitech G400 Razer Kabuto 
Audio
Creative Audigy 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
E8400 DFI LP DK P45-T2RS Sapphire 4850 512MB 2x2GB G.Skill 1066 (5-5-5-15-2T 1.9V) 
Hard DriveOptical DriveOSMonitor
820GB Total LG Super DVD Burner SATA VII Enterprise x64 Gateway FHD2401 
PowerCaseMouseMouse Pad
Corsair VX550W NZXT Source 210 White Logitech G400 Razer Kabuto 
Audio
Creative Audigy 
  hide details  
Reply
post #12 of 16
No. Get rid of the curly brackets and embed the LEFT function as the cell reference in the VLOOKUP function (so you're nesting functions like you would in a programming language.

I can't really write the code for you right now as on phone and coding on that is a nightmare.
post #13 of 16
Thread Starter 
So... Like this then?
Code:
=VLOOKUP(LEFT(D14, 3), Floor, 2, FALSE)
    
CPUMotherboardGraphicsRAM
E8400 DFI LP DK P45-T2RS Sapphire 4850 512MB 2x2GB G.Skill 1066 (5-5-5-15-2T 1.9V) 
Hard DriveOptical DriveOSMonitor
820GB Total LG Super DVD Burner SATA VII Enterprise x64 Gateway FHD2401 
PowerCaseMouseMouse Pad
Corsair VX550W NZXT Source 210 White Logitech G400 Razer Kabuto 
Audio
Creative Audigy 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
E8400 DFI LP DK P45-T2RS Sapphire 4850 512MB 2x2GB G.Skill 1066 (5-5-5-15-2T 1.9V) 
Hard DriveOptical DriveOSMonitor
820GB Total LG Super DVD Burner SATA VII Enterprise x64 Gateway FHD2401 
PowerCaseMouseMouse Pad
Corsair VX550W NZXT Source 210 White Logitech G400 Razer Kabuto 
Audio
Creative Audigy 
  hide details  
Reply
post #14 of 16
Looks good
post #15 of 16
Thread Starter 
Works perfectly. Thanks again for all your help. smile.gif
    
CPUMotherboardGraphicsRAM
E8400 DFI LP DK P45-T2RS Sapphire 4850 512MB 2x2GB G.Skill 1066 (5-5-5-15-2T 1.9V) 
Hard DriveOptical DriveOSMonitor
820GB Total LG Super DVD Burner SATA VII Enterprise x64 Gateway FHD2401 
PowerCaseMouseMouse Pad
Corsair VX550W NZXT Source 210 White Logitech G400 Razer Kabuto 
Audio
Creative Audigy 
  hide details  
Reply
    
CPUMotherboardGraphicsRAM
E8400 DFI LP DK P45-T2RS Sapphire 4850 512MB 2x2GB G.Skill 1066 (5-5-5-15-2T 1.9V) 
Hard DriveOptical DriveOSMonitor
820GB Total LG Super DVD Burner SATA VII Enterprise x64 Gateway FHD2401 
PowerCaseMouseMouse Pad
Corsair VX550W NZXT Source 210 White Logitech G400 Razer Kabuto 
Audio
Creative Audigy 
  hide details  
Reply
post #16 of 16
Quote:
Originally Posted by legoman786 View Post

Works perfectly. Thanks again for all your help. smile.gif

No problem mate smile.gif
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming
Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › [SOLVED] Need an Excel 2010 function, formula, or macro