Need to do vlookup based on whether cell contains @ or not

I am making a worksheet that contains a column for one team and another for its opponent. The second column displays either the other team or @team. In the third column, I want to do a vlookup. The column I need to lookup depends on whether the second column contains "@" or not.

Ideally, I want it to check the cell for the @. If it contains the symbol, it would use the next 3 characters for a Vlookup. If there is no @ present, it would do a vlookup from the other cell. The teams in both columns are all abbreviated to 2-3 characters.

A1	B1	C1
TEAM	TEAM	VLOOKUP (from A1)
TEAM	@TEAM	VLOOKUP (TEAM in B1)

What formula could I use? Please let me know.

Thanks.

Originally Posted by FDibbins

Hi, welcome to the forum

maybe something like this?

=vlookup(IF(LEFT(B1,1)="@",A1,B1),the-rest=of=your=formula,,,,,,,,,,,,,,,,,,,,,,

If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

You were close. You actually had the A1 and B1 switched, but I also had to add another formula in place of the B1 to search without the @. Here's the final formula:
=VLOOKUP(IF(LEFT(B1,1)="@",REPLACE(B1,1,1,""),A1),'Park Factor'!$M$4:$P$33,4,FALSE)

Now I just have to add another vlookup using the results of this formula. How fun.

Originally Posted by FDibbins

what exactly are you trying to achieve here?

Perhaps if you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

I'm working on spreadsheets to use for the different fantasy sports. I'm working on the MLB one right now. I know, it's the end of the season. Hopefully the playoffs take awhile I guess.

The cells are filled with the basic player data, which includes the player's team and the opponents team. If the game is played at the opponent's stadium, the team has an "@" before the team abbreviation. Each stadium the teams play at has it's own park factor. The purpose of the formula you helped me with is to lookup the stadium name of where the game will be played. The next vlookup uses the stadium name to pull the actual park factor from a different list of data. This formula I already know what to enter to make it work. I'm just making the first formula a nested formula in the second vlookup. There isn't a shorter way to do two vlookups using the data from one for the other is there? This is what my final formula looks like:

=VLOOKUP(VLOOKUP(IF(LEFT(G4,1)="@",REPLACE(G4,1,1,""),D4),'Park Factor'!$M$4:$P$33,4,FALSE),'Park Factor'!$C$4:$I$33,2,FALSE)

Originally Posted by FDibbins

Off the top of my head, perhaps you could combine the source tables, so you just do 1 VL?

Not sure. The park factors change regularly, so I end up copying and pasting the new data to the same location in the workbook. I'd love to use the import web data feature, but the webpage doesn't give me the box to click so I can download it automatically. I have it setup with a table of team abbreviations, team names, nicknames, and stadium names. The other table contains all of the park factor data with the identifier being the stadium name. The lookup uses the team abbreviation, but the table I copy and paste doesn't include it.

I did post on another site outside of this one because I didn't know if/when I would get a response through this site. I've made lots of postings on forums where I never received a response, so I'm not putting all my eggs in one basket when I don't know if the site is going to help me or not. I recognized you for your effort though and showed it by commenting in your reputation section. And the discussion is continuing on if there are better options than what I'm using.

http://www.mrexcel.com/forum/excel-q...s-%40-not.html