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"
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"
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.
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"
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:
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 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
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
Cafe Home
Fantasy Football
Fantasy Basketball
Fantasy Hockey
by Bloody Sox » Fri Mar 03, 2006 3:21 pm 
