IF, LEN and VLOOKUP combined

[Mike Stewart]

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.

 

[James006]

Hello,

Could this help :

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

 

[AlanY]

you got the bracket in the wrong place

=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]

Fantastic Alan, thank you very much.

you got the bracket in the wrong place

=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]

you're welcome

 

[omcsarayar]

hey guys, i was wondering if this combination of formulas can help me with my sheets
i also need to manipulate vlookup return value. if from the sheet A it returns "Male", then on the sheet B i need the return to be "M", if not then just blank.

i tried using this formula but it's not working

=IF(LEN(VLOOKUP(A1,'Form responses 1'!C3:F,32,0))=Male,"L",VLOOKUP(VLOOKUP(A1,'Form responses 1'!C3:F,32,0))

any help would be appreciated, thankyouu

 

[Fluff]

You would do better starting a new thread for this question, explaining what you are trying to do, including what the ranges you are looking at are, as you current formula is invalid.