LOOKUP works with only certain numbers

[Dannyg1]

Thanks to some previous help, I am creating a LOOKUP in Excel 97 for use on scorecards for my golf club. However, for some reason, my LOOKUP doesn't work after a certain point in the range.

I have 65 rows of numbers that represent the golfer's handicap index . They are in column A. Column B has an equal number of cells that are the Course Handicap. The LOOKUP I'm using is: =VLOOKUP($D$2,Handicap_List,2,TRUE)

If I enter a number that is within the first 4 rows of Column A, the LOOKUP returns the correct course handicap from column B. If I enter a number that is in row 6 or greater in from column A, the LOOKUP returns the value from B:5. I can't figure out why this is happening. Can you help me?

Below is a copy of the first 7 rows as an example. I hope the formating doesn't change.

Index Handicap
6.0 6
7.0 6
7.1 7
8.1 7
8.2 8
9.2 8

Thanks much.

Dan

 

[just_jon]

Check to see if the underlying format of the cells is the same; that is, are they all general, or some general and some text. In addition, the TRUE part of your VLOOKUP will force an approximate match, rather than an exact match only, when the exact match is not found.

 

[Aladin Akyurek]

Give an example lookup value for which you get a wrong result along with the desired result?

 

[Northerner]

Change your TRUE to FALSE, this ensures an exact match and not an approximate in your search. Your answer from row 6 is the same as row 5, therefore giving the same answer and you've only shown six rows and not seven as state. Does your define name cover the entire area of data.
I might be stating the obvious but sometimes "You can't see the wood for the trees"
Good luck.

 

[IML]

Change your TRUE to FALSE, this ensures an exact match and not an approximate in your search.

Or if you do want to find the closest match with True, ensure that your list is sorted in ascending order by the first column as required to have it function properly.

 

[Dannyg1]

Thanks to all who sent a reply to me. Although I still don't understand why the LOOKUP works through a certain range and not beyond that range, I finally figured out a fix by adding a range statement within the LOOKUP. It now works fine.

Thanks again.

Dan

 

[IML]

One last thought: select Handicap_List from the name box. Is all your data highlighted? And just to confirm: are you looking for an exact match, or the closest match?

 

[Dannyg1]

Thanks for your reply. I don't have the data highlighted. I would think that should be an unnecessary step...isn't it??

RE the match, I'm looking for the closest match. It does work now that I've added the range statement in the formula. I would like to know why without the range it worked up through a certain row and wouldn't work in rows after that. Here's what I suspect, please tell me if you agree.

When I was originally trying to work out the formula--never having done a LOOKUP before, I entered 6 rows of data in two columns. I then wrote the formula and it worked. I then added another 60-some rows of data, but the formula worked only through the initial 6 rows. For the additional rows, the formula would return the result that was in row 6. Unable to get the formula to work for the additional rows, I added the range and it now works. The formula is: VLOOKUP(X11,'Look up'!$C$2:$D$25,2,TRUE). Although I had planned to have 60-some rows, I reduced it to 23 as shown in the formula.

Thanks again.

d

 

[just_jon]

Your range - 6 rows or 60 rows - is all a VLOOKUP will search. And, because you are seeking the nearest match instead of an exact match, the 6th row was always the nearest match for your other data, which would have found their proper nearest matches if the range had been expanded.

VLOOKUP("XXX",A1:A5,1)

will not find XXX in A6 onward.

 

[Dannyg1]

Thanks much.

dg