VLookup with multiple conditions

[noltron]

Hello,
Excel semi-noob here, I have a vlookup issue that has been driving me crazy.

I am working from a sheet (in workbook A) that has a list of usernames in column E. These usernames also exist in column B of Workbook B but they contain an extra five or six charactes at the end of the string.

Example:
Column E of Workbook A
username

Column B of Workbook B
username (xyz)
or
username (xy)

I want to use these usernames in workbook A to lookup their associated 'machine name' in column C of workbook B, BUT only if that row in column D (of worksheet B) contains '5.1'.

I imagine I need some kind of nested if statement within the vlookup to only look at rows of worksheet B where column D contains '5.1'. But also, I need to figure out how to dynamically trim the trailing 5 or 6 characters from the usernames in column B or worksheet B so that the vlookup will recognize the usernames.

I hope this was clear enough, please let me know if you need further clarification. I will be feverishly working on this all day today and any help would be appreciated beyond words.

Thanks,

Noltron

 

[skorpionkz]

could you give a samle of how the usernames looks like?

 

[Andrew Poulsom]

Welcome to MrExcel.

See if you can adapt this:

=INDEX(Sheet2!C1:C100,MATCH(1,INDEX((LEFT(Sheet2!B1:B100,LEN(E1))=E1)*(Sheet2!D1:D100=5.1),),FALSE))

 

[skorpionkz]

try also this:

Excel 2010
	A	B	C	D	E	F
1	username (xyz)	5.1			username	5.1
2	username1 (xy)
Sheet1

Cell Formulas
Range	Formula
B1	=IFERROR(VLOOKUP(LEFT(A1,FIND(" ",A1)-1),$E$1:$F$22,2,0),"")

EDIT: To late... see Andrews post above

 

[noltron]

It's just a string of letters, i.e smithjo

 

[skorpionkz]

Welcome to MrExcel.

See if you can adapt this:

=INDEX(Sheet2!C1:C100,MATCH(1,INDEX((LEFT(Sheet2!B1:B100,LEN(E1))=E1)*(Sheet2!D1:D100=5.1),),FALSE))

Good formula, but I see 1 possible problem in there. If you would search name say "John" and in other table would be name "Johne" formula will match it

 

[Andrew Poulsom]

If there's always a space before the extra data:

=INDEX(Sheet2!C1:C100,MATCH(1,INDEX((LEFT(Sheet2!B1:B100,LEN(E1&" "))=E1&" ")*(Sheet2!D1:D100=5.1),),FALSE))