# IF, LEN and VLOOKUP combined

#### Mike Stewart

##### New Member
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.

### 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
Hello,

Could this help :

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

#### AlanY

##### Well-known Member
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
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))``

you're welcome

Replies
4
Views
59
Replies
20
Views
136
Replies
2
Views
36
Replies
8
Views
109
Replies
2
Views
55