Help with VLOOKUP and LEFT functions

[Sean15]

Hi:

Excel 2010
	B	C	D	E	F	G
2	101203	Cash	#N/A		101	Assets
3	101205	Cash Equiv	#N/A		102..	Liab
4	102207	Intangible	#N/A		103	Revenue
5	102125	Accounts payable	#N/A		104	Exp
6	103129	Billing	#N/A		105	Equity
7	103131	Billing2	#N/A
8	104135	Salary	#N/A
9	104137	Rent	#N/A
10	105614	Equity	#N/A
Sheet1

Cell Formulas
Range	Formula
D2	=VLOOKUP(VALUE(LEFT(B2,3)),$F$2:$G$6,2,0)
D3	=VLOOKUP(VALUE(LEFT(B3,3)),$F$2:$G$6,2,0)
D4	=VLOOKUP(VALUE(LEFT(B4,3)),$F$2:$G$6,2,0)
D5	=VLOOKUP(VALUE(LEFT(B5,3)),$F$2:$G$6,2,0)
D6	=VLOOKUP(VALUE(LEFT(B6,3)),$F$2:$G$6,2,0)
D7	=VLOOKUP(VALUE(LEFT(B7,3)),$F$2:$G$6,2,0)
D8	=VLOOKUP(VALUE(LEFT(B8,3)),$F$2:$G$6,2,0)
D9	=VLOOKUP(VALUE(LEFT(B9,3)),$F$2:$G$6,2,0)
D10	=VLOOKUP(VALUE(LEFT(B10,3)),$F$2:$G$6,2,0)

I believe VLOOKUP is returning #NA because of the "..." in the lookup range but I am not allowed to make changes to column F.

Could you take a look please?


Regards,

Sean

 

[jtakw]

Hi,

There might be other ways, here's a CSE INDEX/MATCH:

Excel 2010
	B	C	D	E	F	G
2	101203	Cash	Assets		101	Assets
3	101205	Cash Equiv	Assets		102..	Liab
4	102207	Intangible	Liab		103	Revenue
5	102125	Accounts payable	Liab		104	Exp
6	103129	Billing	Revenue		105	Equity
7	103131	Billing2	Revenue
8	104135	Salary	Exp
9	104137	Rent	Exp
10	105614	Equity	Equity
Sheet16

Cell Formulas
Range	Formula
D2	{=INDEX(G$2:G$6,MATCH(LEFT(B2,3),LEFT(F$2:F$6,3),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

D2 formula copied down.

 

[markmzz]

Hi:

I believe VLOOKUP is returning #NA because of the "..." in the lookup range but I am not allowed to make changes to column F.

Could you take a look please?

Regards,

Sean

Hi!

Try this too:

=VLOOKUP(LEFT(B2,3)&"...",$F$2:$G$6,2,0)<strike></strike>

Or

=LOOKUP(LEFT(B2,3),LEFT($F$2:$F$6,3),$G$2:$G$6)

Markmzz

 

[Aladin Akyurek]

Did you try using the * wildcard?

=VLOOKUP(LEFT(B2,3)&"*",$F$2:$G$6,2,0)

 

[Sean15]

Thank you very much.

Is there a way to use INDEX and MATCH without CSE? If not, I'll go LOOKUP formula.

Regards,

Sean

 

[markmzz]

Thank you very much.

Is there a way to use INDEX and MATCH without CSE? If not, I'll go LOOKUP formula.

Regards,

Sean

Try this:

=INDEX(G$2:G$6,MATCH(LEFT(B2,3),INDEX(LEFT(F$2:F$6,3),),0))

Markmzz

 

[jtakw]

Thank you very much.

Is there a way to use INDEX and MATCH without CSE? If not, I'll go LOOKUP formula.

Regards,

Sean

Yes, forgot about using wildcards:

Excel 2010
	B	C	D	E	F	G
2	101203	Cash	Assets		101	Assets
3	101205	Cash Equiv	Assets		102..	Liab
4	102207	Intangible	Liab		103	Revenue
5	102125	Accounts payable	Liab		104	Exp
6	103129	Billing	Revenue		105	Equity
7	103131	Billing2	Revenue
8	104135	Salary	Exp
9	104137	Rent	Exp
10	105614	Equity	Equity
Sheet16

Cell Formulas
Range	Formula
D2	=INDEX(G$2:G$6,MATCH(LEFT(B2,3)&"*",F$2:F$6,0))

 

[Aladin Akyurek]

Thank you very much.

Is there a way to use INDEX and MATCH without CSE? If not, I'll go LOOKUP formula.

Regards,

Sean

If those ... are real, that would indicate text values in F. In that case, a VLOOKUP with the * wildcard would simply work (see post #4 ).

 

[Sean15]

Yep, I'll settle for the simple VLOOKUP with * wildcard and I have added INDEX and MATCH with wildcard to my cheat sheet.

Thanks for your help.

Regards,

Sean