You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
What strikes terror into the hearts of Excel beginners? VLOOKUP! Excel power users, though, can't live without it.
Excel MVP Bill Jelen has declared March 25 - 31, 2012 to be VLOOKUP week. He's invited his fellow Excel experts to publish VLOOKUP posts on their sites during the week. At MrExcel.com, Bill will offer five podcasts about VLOOKUP and do giveaways each day. Posts from across the web will be curated and published on a special blog at vlookupweek.wordpress.com.
(The latest report from Bill at 5 pm CST on March 22: VLOOKUP Week has taken on a life of its own. Bill finds himself with VLOOKUP t-shirts, coffee mugs, and a theme song and logo.)
Apart from generating VLOOKUP swag, Bill's real goal is to make the fearful more comfortable with VLOOKUP, and to take the people who already know VLOOKUP to the next level. On his site, he'll be publishing articles for beginners and experts; for example he's planning one on how do a "VLOOKUP left" and another on how to return the row with the *last* matching value.
We're participating, too, thanks to Bill. We'll be publishing a five-part tutorial from him, one segment for each day of the week--with videos.
Before the week starts, you can find out if you're an Excel beginner or an expert. Take a look at the tag cloud below to see if you can define more than five terms. Then at the end of the week check back to see what you've mastered.
If this works out, how about a Pivot Table week
How come we connect next level..
Both VLOOKUP and HLOOKUP can get a little tricky in Excel. Look forward to the week.
Excellent... I'm very excited about this :P
Sounds good ... gotta love that iferror!
thanks! looking forward to it.
If Lookup Value is in Cell C1, Lookup Column is B and Result Column is A, then the “VLOOKUP left” formula will be =OFFSET(B1,MATCH(C1,B:B,0)-1,-1)
Last Matching value
If Lookup Value is in Cell C1, Lookup Column is B and Result Column is A, this array formula will result the last matching value: =OFFSET(B1,MAX(IF(C1=B:B,ROW(B:B)))-1,-1). This formula should be entering as an array formula (Click the formula bar and press Control+Shift+Enter). If you are using Excel 2003, specify the range like B1:B20, instead of entire column B:B.
Intermediate Matching values
If Lookup Value is in Cell C1, Counting of the lookup value is in cell D1, Lookup Column is B and result Column is A, this array formula will result the intermediate matching value: =OFFSET(B1,SMALL(IF(C1=B:B,ROW(B:B)),D1)-1,-1). This formula should be entering as an array formula (Click the formula bar and press Control+Shift+Enter). If you are using Excel 2003, specify the range like B1:B20, instead of entire column B:B.
Sivaprasad, those are extremely useful and bite-sized tips. Thanks for taking the time to share them.
I've been wanting to learn more about vlookups and this is the week to do it!