vlookup with wildcards

[freakofnature]

can't get "*"& cell ref wildcard to work, trying to vlookup in ref to a column of nfl teams 2-3 character abbreviations,
GB vlookup formula
DAL vlookup formula @SEA
and so on ... looking into another sheet that has all teams
with their fantasy points allowed by position in columns..


Team name QB pts RB pts WR pts K pts Total pts
GB 13 14 17 7 sum
DAL 12 12 15 8 sum
.. etc
the @ is what's hanging me up, it won't match @SEA to SEA

 

[freakofnature]

week#	17
@	@SEA		#N/A (formula)
H	CAR
H	CIN		#N/A

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

Team	Gms	QBPts	RB Pts	WR Pts	TE Pts	K Pts	Pts Allow/Gm
ARI		17.5	16.1	21.1	7	7.9	69.6
ATL		16.9	17.4	18.1	6.3	8.2	66.8
BAL		10.6	19.7	16.8	8.1	7.4	62.6
BUF		12.7	21.6	16.7	8.1	8.6	67.8
CAR		14.7	15.6	21.6	6.3	7.1	65.2
CHI		13.7	16.9	20.4	6.1	8.6	65.7

<colgroup><col span="2"><col span="5"><col></colgroup><tbody>
</tbody>

 

[Fluff]

Hi & welcome to the board.

Have a look here
http://www.exceltip.com/lookup-formulas/wildcard-with-vlookup-in-microsoft-excel.html

 

[freakofnature]

Thx for the quick response but that doesn't work, the above column with the Team names is AJ,
=VLOOKUP("*"&AJ2&"*",'2017 FPA Def'!C2:J33,4,FALSE) where C2:J33 is the range for the bottom part of the above post

 

[AhoyNC]

Is it always the last 3 letters you are trying to match? If so try:

=VLOOKUP(RIGHT(AJ2,3),'2017 FPA Def'!C2:J33,4,FALSE)

 

[freakofnature]

will that work if all the AJ cells have either 2 or 3 letters , SEA , CAR , GB, etc ?

 

[freakofnature]

Ok, tested & it works except for ones that have @xx, like @KC, @NO

 

[AhoyNC]

No, maybe this instead. This assumes AJ2 will always be something like @SEA, @GB.

=VLOOKUP(SUBSTITUTE(AJ2,"@",""),'2017 FPA Def'!C2:J33,4,FALSE)

<colgroup><col width="461"></colgroup><tbody>
</tbody>

 

[freakofnature]

Things r lookin up, the subs thing doesn't actually replace any text?

 

[AhoyNC]

It doesn't replace any of your data if that's what you mean.
In the VLOOKUP formula it just drops the @ from cell AJ2, so it's looking up SEA and not @SEA in your team database. Cell AJ2 stays as @SEA.