Ride the Shark! It's VLOOKUP Week March 25-31, 2012

re: Ride the Shark! It's VLOOKUP Week March 25-31, 2012

Scott Fasser — Fri, 30 Mar 2012 21:32:50 GMT

I've been wanting to learn more about vlookups and this is the week to do it!

re: Ride the Shark! It's VLOOKUP Week March 25-31, 2012

anilduttbhargava — Thu, 29 Mar 2012 07:38:10 GMT

Nice Work

Mr. Excel

re: Ride the Shark! It's VLOOKUP Week March 25-31, 2012

Anneliese Wirth — Mon, 26 Mar 2012 20:48:10 GMT

Sivaprasad, those are extremely useful and bite-sized tips. Thanks for taking the time to share them.

re: Ride the Shark! It's VLOOKUP Week March 25-31, 2012

sivaprasadpss — Sat, 24 Mar 2012 07:25:21 GMT

VLOOKUP left

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.

re: Ride the Shark! It's VLOOKUP Week March 25-31, 2012

PL_FL — Fri, 23 Mar 2012 20:24:47 GMT

thanks! looking forward to it.

re: Ride the Shark! It's VLOOKUP Week March 25-31, 2012

PL_FL — Fri, 23 Mar 2012 20:20:35 GMT

Sounds good ... gotta love that iferror!

re: Ride the Shark! It's VLOOKUP Week March 25-31, 2012

adelm — Fri, 23 Mar 2012 18:31:05 GMT

Excellent... I'm very excited about this :P

re: Ride the Shark! It's VLOOKUP Week March 25-31, 2012

Rblack40 — Fri, 23 Mar 2012 18:21:28 GMT

Both VLOOKUP and HLOOKUP can get a little tricky in Excel. Look forward to the week.

re: Ride the Shark! It's VLOOKUP Week March 25-31, 2012

ajaytilewale — Fri, 23 Mar 2012 17:50:32 GMT

How come we connect next level..

re: Ride the Shark! It's VLOOKUP Week March 25-31, 2012

zyzzyva57 — Fri, 23 Mar 2012 10:13:25 GMT

If this works out, how about a Pivot Table week