[Solved] LOOKUP detects values that aren't there

[Ravel]

Hello,

first let me thank you for your time.

So for a hobby of mine (Pen and Paper Roleplay) I'm creating a spreadsheet to quicken the creation of characters, with requires checking if certain entries have been made.

So I have created a table with 30 rows and 3 columns. Lets call it A1:C30.

So A1:A20 can contain a number, if an entry has been made by the user in a cell somewhere else in the spreadsheet. These entries aren't sorted nummerically. B1:B30 contains the numbers 1-30. For convenience I filled C1:C30 just with 1's. Because thats what I need either a number, for which I can check with a followup formula in another cell (=if(E1>0;"True";"False")).

So if I now in another cell want to search for a certain number in A1:A20, I use the following.
=LOOKUP(B1;A$1:A$20;C$1:C$30)

To make it short: I hope to that the formula produces a 1 if it detects one of the numbers 1-30 in the cells A1:A20.

So far the cells A1 and A10 contain entries, 1 and 6.

Right now I have the formula copied 11 times. Say E1:E11 but all it does produce is 1. For every cell that contains the formula. In E11 the formula is =LOOKUP(B11;A$1:A$20;C$1:C$30) and it produces a 1 (even if delete C11 and turn it blank).

Right now the formula tells me that in the Cells A1:A20 all the individual Numbers 1-11 are contained. A1:A20 only contain a formula only produces a result in A1 and A10, the other cells are empty.

Why?

And what formula would check A1:A20 for the numbers 1-30 (disregarding order) and produce a numerical value?

Of course I could work around this by producing something like:

=IF(A1=1;1;if(A1=2;1;if(A1=3;1;if(A1=4.... but there has to be an easier way.

Again thank you for your time.

[FJCC]

LOOKUP() needs to have a sorted search vector for it to work correctly. If you just need to know whether B1 is present in the range A1:A20, you can use COUNTIF(). It will return how many times the search criterion appears in the list

Code: Select all

=COUNTIF(A1:A20;B1)

will return zero if B1 is not in A1:A20.

[Ravel]

Thank you very much on my way to work right now. Will check this solution in the evening, but it sounds very promising.

[Villeroy]

menu:Tools>Options>Calc>Calculation...
Regular expressions in formulas = OFF (unless you are sure that you need to match regexes)
Search criteria = and <> must apply to whole cells = ON (otherwise "tax" will be matched in "taxonomy")

and by the way, even with a sorted search vector, LOOKUP does not do what most people expect it to do.

[Ravel]

Thank you two for your quick help. CountIF worked and allowed me to achieve another function I needed.