Return the first text value from column where corresponding columns first two letter match letters

[RustyHook]

Hello Mr Excel Community

My problem is as follows.
In Sheet1 column B:B I have a list of two symbol initials than can comprise of letters and numbers.
B:B
EK
BR
SK
TG
TK
ETC..


In sheet 2 column A:A I have a list where the first two values will comprise of the values listed in B:B. I then have another column in the same sheet BW:BW.

A:A BW:BW
4T435 ASE
CA342 EDF
BR112 EDF
SK331 ASE
LL121 DNT
4U145 DNT
etc


What I require is a formula in Sheet1 column C:C that I can drag down and return the correct value in sheet 2 BW:BW that corresponds to the first two letters in column A:A of Sheet1.

I have previously tried to solve this using
=VLOOKUP(LEFT(B1,2)&"*",'Sheet2'!A$2:BZ$2200,58,FALSE)
But this is the incorrect logical order as I need the first two letters to the left to apply to my column A:A in sheet2.

All help is welcome

Kind regards.

 

[gaz_chops]

Try
=INDEX(Sheet2!BW:BW,MATCH(Sheet1!B2&"*",Sheet2!A:A))

 

[Comfy]

Your formula looks correct except the inclusion of the LEFT() function, although it should still work.

Excel 2010

	A	B
EK
BR	EDF
SK	ASE
TG
TK

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]#N/A[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]#N/A[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]#N/A[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=VLOOKUP(A1&"*",Sheet2!$A$1:$B$6,2,FALSE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Excel 2010

	A	B
4T435	ASE
CA342	EDF
BR112	EDF
SK331	ASE
LL121	DNT
4U145	DNT

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

</tbody>

Sheet2​

 

[Jonmo1]

I don't see why the formula you already tried won't work..

I have previously tried to solve this using
=VLOOKUP(LEFT(B1,2)&"*",'Sheet2'!A$2:BZ$2200,58,FALSE)

That will do exactly what you're describing...
Though you don't need the LEFT function if the value in column B is already only 2 characters...
=VLOOKUP(B1&"*",'Sheet2'!A$2:BZ$2200,58,FALSE)

 

[gaz_chops]

If not found returns a blank
=IFERROR(INDEX(Sheet2!$B:$B,MATCH(Sheet1!B1&"*",Sheet2!A:A,0)),"")

 

[RustyHook]

Thankyou all very much

For some reason when using the VLookup I can only get an answer of #N/A. Even without the unnecessary (LEFT(B1,2)

The index match solution works like a charm and the IFERROR neatens things up nicely. Thankyou Gaz_chops

 

[Jonmo1]

The index match solution works like a charm and the IFERROR neatens things up nicely. Thankyou Gaz_chops

Which Index/Match ??
If it's the one from post#5, then that is basically the same as the Vlookup, so it should work as well.

If it's the one from post#2, then I suspect you're getting 'incorrect' values, because it requires the data in Sheet2!A:A to be sorted ascending.
Which according to your example, it's defiately not.


Something else is wrong here..