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]

[TABLE="width: 236"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD] week#[/TD]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]@[/TD]
[TD]@SEA[/TD]
[TD][/TD]
[TD="align: center"]#N/A (formula)[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]CAR[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]CIN[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 554"]
<colgroup><col span="2"><col span="5"><col></colgroup><tbody>[TR]
[TD]Team[/TD]
[TD]Gms[/TD]
[TD] QBPts [/TD]
[TD] RB Pts[/TD]
[TD] WR Pts[/TD]
[TD] TE Pts[/TD]
[TD] K Pts[/TD]
[TD] Pts Allow/Gm[/TD]
[/TR]
[TR]
[TD]ARI[/TD]
[TD][/TD]
[TD="align: right"]17.5[/TD]
[TD="align: right"]16.1[/TD]
[TD="align: right"]21.1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7.9 [/TD]
[TD] 69.6[/TD]
[/TR]
[TR]
[TD]ATL[/TD]
[TD][/TD]
[TD="align: right"]16.9[/TD]
[TD="align: right"]17.4[/TD]
[TD="align: right"]18.1[/TD]
[TD="align: right"]6.3[/TD]
[TD="align: right"]8.2[/TD]
[TD] 66.8[/TD]
[/TR]
[TR]
[TD]BAL[/TD]
[TD][/TD]
[TD="align: right"]10.6[/TD]
[TD="align: right"]19.7[/TD]
[TD="align: right"]16.8[/TD]
[TD="align: right"]8.1[/TD]
[TD="align: right"]7.4[/TD]
[TD] 62.6[/TD]
[/TR]
[TR]
[TD]BUF[/TD]
[TD][/TD]
[TD="align: right"]12.7[/TD]
[TD="align: right"]21.6[/TD]
[TD="align: right"]16.7[/TD]
[TD="align: right"]8.1[/TD]
[TD="align: right"]8.6 [/TD]
[TD] 67.8[/TD]
[/TR]
[TR]
[TD]CAR[/TD]
[TD][/TD]
[TD="align: right"]14.7[/TD]
[TD="align: right"]15.6[/TD]
[TD="align: right"]21.6[/TD]
[TD="align: right"]6.3[/TD]
[TD="align: right"]7.1[/TD]
[TD] 65.2[/TD]
[/TR]
[TR]
[TD]CHI[/TD]
[TD][/TD]
[TD="align: right"]13.7[/TD]
[TD="align: right"]16.9[/TD]
[TD="align: right"]20.4[/TD]
[TD="align: right"]6.1[/TD]
[TD="align: right"]8.6[/TD]
[TD] 65.7[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.

[TABLE="width: 461"]
<colgroup><col width="461"></colgroup><tbody>[TR]
[TD="width: 461"]=VLOOKUP(SUBSTITUTE(AJ2,"@",""),'2017 FPA Def'!C2:J33,4,FALSE)
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.