Vlookup Question

Larry · May 6th, 2002, 11:13 AM

Can you specify in a Vlookup to being the lookup where the last on left off?

For example, I have a Vlookup the goes down 200 rows and it is looking in a table that contains 5000 rows. Can I specify for the lookup to continue looking from the last record where it found a match, or does it have to begin the lookup at the top of the table?

Any info will be appreciated.

Corticus · May 6th, 2002, 11:17 AM

You could put a label at the end of each row that corresponds to the row address. When your VLookup retrieves the data you want, have it also pull that label. You could then have in your Vlookup command a variable for your range instead of an explicit range, with the value of the variable being set from the returned value of yur initial lookup. Hope that helps.

zacemmel · May 6th, 2002, 11:19 AM

Larry,

You can do what you are describing. However, you can't do it very easily (if at all) with a vlookup. I would suggest doing an if type statement.

Larry · May 6th, 2002, 11:22 AM

Corticus,

Could you possibly show some of the logic for this? I am a newbie and I'm not sure I could translate what you said into a functional formula.

Thanks again,

zacemmel · May 6th, 2002, 11:24 AM

Try something like that. I did mine on a small range, and it seemed to work ok. Let me know if you have any questions.

=VLOOKUP(B1, INDIRECT("a"&MATCH(B1,A1:A10,0)&":a10"),1,0)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 6th, 2002, 11:13 AM	#1
Larry New Member Join Date: Apr 2002 Location: Plano, Texas Posts: 29	Can you specify in a Vlookup to being the lookup where the last on left off? For example, I have a Vlookup the goes down 200 rows and it is looking in a table that contains 5000 rows. Can I specify for the lookup to continue looking from the last record where it found a match, or does it have to begin the lookup at the top of the table? Any info will be appreciated.

May 6th, 2002, 11:17 AM	#2
Corticus Board Regular Join Date: Apr 2002 Location: Sarasota, FL Posts: 1,539	You could put a label at the end of each row that corresponds to the row address. When your VLookup retrieves the data you want, have it also pull that label. You could then have in your Vlookup command a variable for your range instead of an explicit range, with the value of the variable being set from the returned value of yur initial lookup. Hope that helps.

May 6th, 2002, 11:19 AM	#3
zacemmel Board Regular Join Date: Apr 2002 Location: Redmond, WA Posts: 636	Larry, You can do what you are describing. However, you can't do it very easily (if at all) with a vlookup. I would suggest doing an if type statement.

May 6th, 2002, 11:22 AM	#4
Larry New Member Join Date: Apr 2002 Location: Plano, Texas Posts: 29	Corticus, Could you possibly show some of the logic for this? I am a newbie and I'm not sure I could translate what you said into a functional formula. Thanks again,

May 6th, 2002, 11:24 AM	#5
zacemmel Board Regular Join Date: Apr 2002 Location: Redmond, WA Posts: 636	Try something like that. I did mine on a small range, and it seemed to work ok. Let me know if you have any questions. =VLOOKUP(B1, INDIRECT("a"&MATCH(B1,A1:A10,0)&":a10"),1,0)