(v)lookup stops searching miday through sorted array.

[Bishop14]

I have a simple ascending array in column A. 1 through 10. I have a corresponding array in column C from which I wish to extract a value. both vlookup and lookup will search through A values 1 thru 7 and return the correct value from column C. Anything beyond seven returns the value for 7. What is happening here, and how do I correct it?

Specifics might be usefull.

The code I’m using: Seach criterion in D5
D6 = INDEX(C1:C8, MATCH(D5, A1:A8,0))

In Column A
1 180005
2 180006
3 180007
4 180008
5 180009
6 180010
7 180011
8 180012

In Column C
1 0001
2 0002
3 0002
4 0002
5 0003
6 0004
7 0004
8 0005

 When D5 =        180006, D6 = 0002
                  180008, D6 = 0002
                  180009, D6 = 0003
                  180010, D6 = 0003
                  180011, D6 = 0003
                  180012, D6 = 0003

Something is causing the function (also using lookup and vlookup) to hit a ceiling at line 5. ???

[mariosv]

I can’t see the issue, please test if with attached file it works.

Test file

.

[mariosv]

Looks the cell is formatted as text, so anything introduced in it, it’s done as a literal text. Use [Ctrl+1] and Number tab to see what is the selected format.

[Bishop14]

Your test file works like a charm. I’ve believed that the formula was correct, that there was something else happening to impose the ceiling on the INDEX/MATCH as well as the LOOKUP and VLOOKUP formulas.

In addition, there is another bug bothering me: when I enter the formula in my spreadsheet, the cell displays the text of the formula, but not the value the formula returns. I note that in your file, the tools/options/view page you have unchecked the formula box. This supposedly will mask the formula and display the result. In my file, the same box is unchecked as well, yet the formula is displayed, not the value returned. When I use the formula wizard to enter the formula, the wizard shows the return value, and it is correct for any value entered in the search criterion.

The cell shows the formula, not the result, regardless of whether the display formula box is checked or not. This all leads me to believe that maybe I need to simply download another copy of libre office.

[mariosv]

Please use the comment.

[erAck]

If your cells show the formula, together with the symptoms you described in your original question, it is very likely that your cells are formatted to Text, which forces any input to stay a text string instead of evaluating it as formula, and numbers entered will also stay text, which makes functions like VLOOKUP() and MATCH() fail that expect a range to be sorted but numbers sort before text so if you mix numbers and text they are in fact not sorted and the result is arbitrary.