IF, LEN and VLOOKUP combined

Mike Stewart

New Member
Joined
Mar 19, 2019
Messages
2
Hello.

I'm struggling with a formula using IF, LEN and VLOOKUP. I need to manipulate what VLOOKUP returns. If it returns a 10 character string I need to only pull through the first 5 characters, otherwise return the entire string.



=IF(LEN(VLOOKUP(A2,'[Powys Users.xlsx]DataSheet'!$A$1:$C$10000,3,0)=10),LEFT(VLOOKUP(A2,'[Powys Users.xlsx]DataSheet'!$A$1:$C$10000,3,0),5),VLOOKUP(A2,'[Powys Users.xlsx]DataSheet'!$A$1:$C$10000,3,0))



Column A in my "Working" spreadsheet contains unique values. I'm putting the formula into cell B2 (and replicating down entire column) and referencing another spreadsheet (Powys Users.xlsx). Column A in the referenced spreadsheet contains unique values (and will somewhere have a match to my Working spreadsheet's A column entries) and its column 3 contains a text string. If this text string is 10 characters long I want to pull through just the first 5 characters, otherwise pull through the entire string.


At the moment it's just returning 5 characters no matter what is found.



I've cut it down for testing to: =IF(LEN(VLOOKUP(A2,'[Powys Users.xlsx]DataSheet'!$A$1:$C$10000,3,0)=10),"Yes", "No")



This is returning "Yes" no matter what it finds in the referenced spreadsheet. I know some matches are 10 chars and others 5 or 6.


Any help will be much appreciated.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

Could this help :

Code:
=LEFT(VLOOKUP(A2,'[Powys Users.xlsx]DataSheet'!$A$1:$C$10000,3,0),5)
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,170
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you got the bracket in the wrong place

Code:
=IF(LEN(VLOOKUP(A2,'[Powys Users.xlsx]DataSheet'!$A$1:$C$10000,3,0)[COLOR="#FF0000"])=10[/COLOR],LEFT(VLOOKUP(A2,'[Powys Users.xlsx]DataSheet'!$A$1:$C$10000,3,0),5),VLOOKUP(A2,'[Powys Users.xlsx]DataSheet'!$A$1:$C$10000,3,0))
 

Mike Stewart

New Member
Joined
Mar 19, 2019
Messages
2
Fantastic Alan, thank you very much.

you got the bracket in the wrong place

Code:
=IF(LEN(VLOOKUP(A2,'[Powys Users.xlsx]DataSheet'!$A$1:$C$10000,3,0)[COLOR=#ff0000])=10[/COLOR],LEFT(VLOOKUP(A2,'[Powys Users.xlsx]DataSheet'!$A$1:$C$10000,3,0),5),VLOOKUP(A2,'[Powys Users.xlsx]DataSheet'!$A$1:$C$10000,3,0))
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,170
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,109,037
Messages
5,526,397
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top