Excel Query (Last one)

choyaa

I know I've posted a few recently, I hope people don't mind me posting one final query.


I have to following code and it returns a value, however is it possible to remove the first two characters, is this possible?


=VLOOKUP(A30,'second tab'!A:IV,MATCH(Template!K29,'second tab'!1:1,0),0)

bod1467

You could use the MID command.

Check the Excel help docs.

choyaa

I've placed the following piece of code into it from the Excel Help documents, it's still not working.


=RIGHT(a30, LEN(A30)-2,VLOOKUP(A30,'second tab'!A:IV,MATCH(Template!K29,'second tab'!1:1,0),0)

Le_Kirk

=RIGHT(A30,LEN(A30)-2) is correct. Note that you have a space between your comma after a30 and before LEN.
What happens when you press enter? You certainly have a parenthesis (bracket) missing - you have 4 opening and only 3 closing. Your VLOOKUP formula is incorrect. The formula should start with =VLOOKUP(A1,B1:C20,2) where A1 contains the value you are trying to find, B1:C20 is the range of data, i.e. where to look and 2 is the column offset. What I always do in these circumstances is to build the formula by stages. Set your lookup range and populate it with your data, then add the LOOKUP formula in simple terms gradually adding complexity

choyaa

I've tried the following but an error message occurs, it advised that there are too many arguments.


=RIGHT(K30,LEN(K30)-2),(VLOOKUP(A30,'tab2'!A:IV,MATCH,K29,'tabs'!1:1,0)0)

bod1467

RIGHT(Text,Length) ... that's what the RIGHT command entails.

Length ... you need to use the LEN command, yes, but you need to use it on the VLOOKUP result.

=RIGHT(VLOOKUP(A30,'second tab'!A:IV,MATCH(Template!K29,'second tab'!1:1,0),0),LEN(VLOOKUP(A30,'second tab'!A:IV,MATCH(Template!K29,'second tab'!1:1,0),0))-2)

choyaa

bod1467 wrote: »

RIGHT(Text,Length) ... that's what the RIGHT command entails.

Length ... you need to use the LEN command, yes, but you need to use it on the VLOOKUP result.

=RIGHT(VLOOKUP(A30,'second tab'!A:IV,MATCH(Template!K29,'second tab'!1:1,0),0),LEN(VLOOKUP(A30,'second tab'!A:IV,MATCH(Template!K29,'second tab'!1:1,0),0))-2)

That works to perfection, however I don't quite understand it - can you run through this please? Sorry to be a pain but Excel is new to me and I am still getting my head around it.

bod1467

You know how the VLOOKUP works already - yes?

VLOOKUP returns the value you're looking for, based on e.g. cell A30.
LEN gets the length of that value (which is why it needs to be within the LEN() command as well - the length of A30 is meaningless; it is the length of the VLOOKUP value based on A30 that matters).
RIGHT returns required characters from the VLOOKUP value.