Any Excel Gurus here? - Fantasy Baseball Cafe 2014 Fantasy Baseball Cafe
100% Deposit Bonus for Cafe Members!

Return to Baseball Leftovers

Any Excel Gurus here?

Moderator: Baseball Moderators

Any Excel Gurus here?

Postby Bloody Sox » Fri Mar 03, 2006 3:21 pm

I'm trying to put together my draft day planning masterpiece in Excel, and I'm stuck trying to lookup a string value in a table consisting of 20 columns, any of which could contain the data. I have no problem using MATCH or VLOOKUP to lookup a value in one of the columns, but I can't figure out how to look up across ALL columns. I tried to figure out how to use INDEX to get it to work, but had no luck there either. I just need to know if the string value I'm looking up lives in that table or not.

Any help would be greatly appreciated and I'm happy to share my work once I've finished it up.

Thanks!
"The government cannot give to anyone anything that it does not first take from someone else"
Bloody Sox
Major League Manager
Major League Manager

User avatar

Posts: 2148
(Past Year: 18)
Joined: 27 Feb 2006
Home Cafe: Baseball
Location: Fenway Park, RF Roof Deck

Postby Matthias » Fri Mar 03, 2006 3:30 pm

Hlookup
Matthias
General Manager
General Manager


Posts: 4860
Joined: 16 Mar 2005
Home Cafe: Baseball

Postby Bloody Sox » Fri Mar 03, 2006 3:32 pm

HLOOKUP is the compliment to VLOOKUP, which only looks up into one row at a time. I want to lookup into 20 rows/columns without having to call HLOOKUP/VLOOKUP 20 times and OR them all together. Thoughts?
"The government cannot give to anyone anything that it does not first take from someone else"
Bloody Sox
Major League Manager
Major League Manager

User avatar

Posts: 2148
(Past Year: 18)
Joined: 27 Feb 2006
Home Cafe: Baseball
Location: Fenway Park, RF Roof Deck

Postby slomo007 » Fri Mar 03, 2006 3:49 pm

=VLOOKUP($B2,TF2!$A$1:$G$2881,2,FALSE)

This formula would take the value in B2, and search the entire TF2 tab, starting from cell A1 - G2881, move two spots to the right and take that value.

Don't be confused by the 'TF2' tab, I just took it from one of my existing spreadsheets, you can replace that with sheet1 or whatever.
slomo007
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicEagle Eye
Posts: 11960
Joined: 31 May 2003
Home Cafe: Baseball

Postby Bloody Sox » Fri Mar 03, 2006 3:55 pm

This formula would take the value in B2, and search the entire TF2 tab, starting from cell A1 - G2881, move


See, the problem is that VLOOKUP only searches in the first column of the range specified, not all columns. So in your case, it'd only lookup the B2 value in Column A, not in Columns B-G.

My problem is that I don't know which of the columns in the table my data is in.
"The government cannot give to anyone anything that it does not first take from someone else"
Bloody Sox
Major League Manager
Major League Manager

User avatar

Posts: 2148
(Past Year: 18)
Joined: 27 Feb 2006
Home Cafe: Baseball
Location: Fenway Park, RF Roof Deck

Postby slomo007 » Fri Mar 03, 2006 4:06 pm

Bloody Sox wrote:
This formula would take the value in B2, and search the entire TF2 tab, starting from cell A1 - G2881, move


See, the problem is that VLOOKUP only searches in the first column of the range specified, not all columns. So in your case, it'd only lookup the B2 value in Column A, not in Columns B-G.

My problem is that I don't know which of the columns in the table my data is in.


Sorry about that. I know what you're trying to do, it's very difficult to explain but see the following page for an example:

http://www.techonthenet.com/excel/formu ... lookup.php
slomo007
Hall of Fame Hero
Hall of Fame Hero

User avatar
CafeholicEagle Eye
Posts: 11960
Joined: 31 May 2003
Home Cafe: Baseball

Postby Bloody Sox » Fri Mar 03, 2006 4:14 pm

No problem - thanks for the link. It still doesn't quite get me there however... the combo VLOOKUP/MATCH still assumes that I have a column header that I can match on. I don't have such an animal.

I have unique string data in cells A1:L12, for example. I want to know if my string data in M1 is a match to any of the cells in the region A1:L12.

The data in my table is not ordered in any particular way... I don't have one list of players, and another for HRs, another for Steals, etc. that I can match on to return the proper data. In fact, I don't even want to return data - I just want to know if I have a match.

I can use OR and embed MATCH in it 12 times, but that's hideous.

Thanks for your help!
"The government cannot give to anyone anything that it does not first take from someone else"
Bloody Sox
Major League Manager
Major League Manager

User avatar

Posts: 2148
(Past Year: 18)
Joined: 27 Feb 2006
Home Cafe: Baseball
Location: Fenway Park, RF Roof Deck

Re: Any Excel Gurus here?

Postby Amazinz » Fri Mar 03, 2006 6:18 pm

Bloody Sox wrote:I just need to know if the string value I'm looking up lives in that table or not.


=SUMPRODUCT(ISNUMBER(SEARCH(M1,A1:L12))+0)>0

Returns TRUE or FALSE
Image
Maine has a good swing for a pitcher but on anything that moves, he has no chance. And if it's a fastball, it has to be up in the zone. Basically, the pitcher has to hit his bat. - Mike Pelfrey
Amazinz
Mod in Retirement
Mod in Retirement

User avatar
CafeholicFantasy ExpertCafe WriterCafe RankerMock(ing) DrafterEagle EyeWeb SupporterPick 3 Weekly WinnerSweet 16 SurvivorLucky Ladders Weekly Winner
Posts: 18800
Joined: 16 Mar 2004
Home Cafe: Baseball
Location: in Canada, toughening up figure skaters

Postby Bloody Sox » Fri Mar 03, 2006 6:20 pm

SUMPRODUCT(ISNUMBER(SEARCH(M1,A1:L12))+0)>0


Dude - that is Amazinz!!! Thanks!
"The government cannot give to anyone anything that it does not first take from someone else"
Bloody Sox
Major League Manager
Major League Manager

User avatar

Posts: 2148
(Past Year: 18)
Joined: 27 Feb 2006
Home Cafe: Baseball
Location: Fenway Park, RF Roof Deck


Return to Baseball Leftovers

Who is online

Users browsing this forum: No registered users and 10 guests

Forums Articles & Tips Sleepers Rankings Leagues


Today's Games
Friday, Oct. 24
(All times are EST, weather icons show forecast for game time)

Kansas City at San Francisco
(8:07 pm)

  • Fantasy Baseball
  • Article Submissions
  • Privacy Statement
  • Site Survey 
  • Contact