LEFT() with a VLOOKUP()

[almagg]

Col G has #s e.g.,

US468
US043
IT008
NL001

The source column has e.g.,

US468: Tyco Marinette Operations
US043: Tigard, OR
IT008: Austria

i want to search the source for the first 5 character match and return the value a number of columns over. i initially wrote this, but it returns the first 5 characters of the value in the 28th column:

=IF(ISNA(LEFT(VLOOKUP(F3,'[TFP nonservice Monthly Hours Matrix (FY11 June).xlsx]report'!$F$6:$AG$161,28,FALSE),5)),"NA",IF(LEFT(VLOOKUP(F3,'[TFP nonservice Monthly Hours Matrix (FY11 June).xlsx]report'!$F$6:$AG$161,28,FALSE),5)=0,AJ3,LEFT(VLOOKUP(F3,'[TFP nonservice Monthly Hours Matrix (FY11 June).xlsx]report'!$F$6:$AG$161,28,FALSE),5)))

no, we do not want to add a column with just the 5 characters, to the source (each month) i.e., as little user interaction as possible.

thanks for any help.

 

[T. Valko]

Col G has #s e.g.,

US468
US043
IT008
NL001

The source column has e.g.,

US468: Tyco Marinette Operations
US043: Tigard, OR
IT008: Austria

i want to search the source for the first 5 character match and return the value a number of columns over. i initially wrote this, but it returns the first 5 characters of the value in the 28th column:

=IF(ISNA(LEFT(VLOOKUP(F3,'[TFP nonservice Monthly Hours Matrix (FY11 June).xlsx]report'!$F$6:$AG$161,28,FALSE),5)),"NA",IF(LEFT(VLOOKUP(F3,'[TFP nonservice Monthly Hours Matrix (FY11 June).xlsx]report'!$F$6:$AG$161,28,FALSE),5)=0,AJ3,LEFT(VLOOKUP(F3,'[TFP nonservice Monthly Hours Matrix (FY11 June).xlsx]report'!$F$6:$AG$161,28,FALSE),5)))

no, we do not want to add a column with just the 5 characters, to the source (each month) i.e., as little user interaction as possible.

thanks for any help.

Try something like this...

=VLOOKUP(F3&"*",...........

 

[almagg]

Good Call !

thanks T

 

[T. Valko]

Good Call !

thanks T

You're welcome. Thanks for the feedback!