1. ## VLookup (Office97 SR-2b)

How do you define range in vertical look-up function. I have an excel file with 3 spreadsheets and 17-18 ranges. I want to know how to define range.
Example: =VLOOKUP(H3,S26,3,TRUE)
H3 = 27

Can you refer to a cell using a CONCATENATE function for a VLookup?

2. ## Re: VLookup (Office97 SR-2b)

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)

3. ## Re: VLookup (Office97 SR-2b)

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

4. ## Re: VLookup (Office97 SR-2b)

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...

5. ## Re: VLookup (Office97 SR-2b)

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 PART-type 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

6. ## Re: VLookup (Office97 SR-2b)

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

7. ## Re: VLookup (Office97 SR-2b)

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.

8. ## Re: VLookup (Office97 SR-2b)

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.

9. ## Re: VLookup (Office97 SR-2b)

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.

10. ## Re: VLookup (Office97 SR-2b)

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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•