Results 1 to 10 of 10
Thread: VLookup (Office97 SR2b)

20020906, 15:14 #1
 Join Date
 Jul 2001
 Posts
 30
 Thanks
 0
 Thanked 0 Times in 0 Posts
VLookup (Office97 SR2b)
How do you define range in vertical lookup function. I have an excel file with 3 spreadsheets and 1718 ranges. I want to know how to define range.
Example: =VLOOKUP(H3,S26,3,TRUE)
H3 = 27
S26 = (CONCATENATE("'pc120_lifetime'!",S23))
Can you refer to a cell using a CONCATENATE function for a VLookup?

20020906, 15:50 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VLookup (Office97 SR2b)
You specify the range by specifying the upper left corner and the lower right corner seperated by a colon. Therefore, to specify the range that starts in A1 and goes to D26 you would use A126.
I don't understand what you are trying to do. Are you trying to put the lookup table range into a cell, and then use that cell in the vlookup? In your example, what is in cell S23?
If S23 contains text that would be a valid range (like A126), then I think that what you want is:
=VLOOKUP(H3,INDIRECT(S26),3,TRUE)Legare Coleman

20020907, 20:12 #3
 Join Date
 Sep 2002
 Location
 St. Columb, Cornwall, England
 Posts
 4
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VLookup (Office97 SR2b)
I am not sure that you are using VLookup correctly but to answer your question a range comprises a selection of cells that are at least 2 consecutive rows deep and 2 consecutive columns wide, the first column must be alphabetically sorted for VLookup to work.
You are attempting to use the value (27) in H3 to locate a near match (TRUE), having found that match (FALSE finds an exact match) you then look at the cell in the third (3) column of your range and return that value to the cell containing the formula, but S26 is not a valid range (B1:G7 would be, or a named range)
KR

20020907, 20:44 #4
 Join Date
 Aug 2002
 Location
 Brooklyn, New York, USA
 Posts
 176
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VLookup (Office97 SR2b)
Just a quick comment on your response:
You are correct that the first column must be sorted in ascending order for VLOOKUP to work, but ONLY if the fourth argument is TRUE. If you sent the fourth argument to FALSE, the lookup range need not be in ascending order.
Editorial comment here: in the dozen or so years I have worked with Excel in banking environments, I have never encountered a situation where the fourth argument needs to be set to TRUE. When the fourth argument is set to TRUE, the lookup range (as mentioned above) does need to be in ascending order, AND Excel will not return the #N/A error if a match isn't found. Instead, Excel returns the nearest lesser value in the lookup range. In my experience, users always want either the #N/A error (which indicates the lookup item is not in the lookup range), or an exact value from the lookup range based on the lookup item.
Hope I wasn't too unclear...

20020907, 21:55 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: VLookup (Office97 SR2b)
Another editorial comment disagreeing with the last editorial comment. I usually use MATCH rather than VLOOKUP, but the same holds true.
I have found if you lookup TEXT you want an exact match, but if you LOOKUP numbers you tend to use an approximate match. I include PARTtype NUMBERS, ZIP codes, Phone Numbers, SS# as TEXT, they are NOT real numbers. Numbers are things you add, multiply, etc. Those other "numbers" are not really numbers!
There are many instances where one might want an approximate match. If you have a TaxTable where you have rates for (for example) every $10,000. you would not want to be told that your value of $25,232.26 is not in the table (the table would have to be too large for EVERY possibility. You want an approximate match and work from there. (Though I would imagine that these tables are being replaced by their underlying formulas)
Also if you have measured, for example, Viscosities at 5 different temperatures. You would want the Vlookup to give you the approximate value of the viscosity and then do some interpolation to get an estimate.
Steve

20020908, 07:46 #6
 Join Date
 Sep 2002
 Location
 St. Columb, Cornwall, England
 Posts
 4
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VLookup (Office97 SR2b)
Thank you for both of the editorial comments, it is nice to see such an interest in correctness; even at 65 (but an amateur), one is never too old to learn!
I will however stick to sorting my first column as it makes spotting of errors and omissions visually easier and the sorting of the table is easily achieved.
KR

20020908, 13:57 #7
 Join Date
 Aug 2002
 Location
 Brooklyn, New York, USA
 Posts
 176
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VLookup (Office97 SR2b)
Well, your response is exactly why I described my comments as "editorial". I certainly didn't want to sound dogmatic and I figured if my limited and singular use of Excel did not reveal to me why users might want an approximate match, then someone would point such examples out to me.
So I thank you for your instructive answer.

20020909, 13:02 #8
 Join Date
 Jul 2001
 Posts
 30
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VLookup (Office97 SR2b)
What I think the User who asked me about doing this is asking...
Can you refer to a cell that is using a CONCATENATE function (concatenating the names of the ranges). I don't see this working, but wanted to question it here.

20020909, 13:41 #9
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VLookup (Office97 SR2b)
Did you try the last formula in my response above? It should work as long as the concatenate function produces a string that is a valid range reference.
Legare Coleman

20020909, 16:15 #10
 Join Date
 Jul 2001
 Posts
 30
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VLookup (Office97 SR2b)
Was able to have User test it today (using the INDIRECT function) and it worked *great*. Thanks for showing me something new and for your help.