Lookup & unmatches lookup value

[verisme]

Hello everyone, ive got a question about lookup, in lookup function there is a remarks that say :

"If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value."

"If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP gives the #N/A error value."

how can i get the result to be "You have type the wrong data" if i've type that doesnt match the list of the lookup value ?

[teylyn]

try

=IF(ISNA(LOOKUP(a1,b1:b100)),"you have the wrong data type",LOOKUP(a1,b1:b100)))

[JBeaucaire]

If you posted examples of your actual lookup formulas, this would be easier, but there are multiple ways to do this, all involve NOT using the LOOKUP() formula.

Rather, you would use VLOOKUP(), HLOOKUP() or INDEX/MATCH().

If your original formula looked like this:

=LOOKUP(A1, C1:C100, D1:D100)

...then you could replace that with:

=IF(ISNUMBER(MATCH(A1, C1:C100, 0)), INDEX(D1:D100, MATCH(A1, C1:C100, 0)), "Wrong data")

Another benefit of this formula is the data no longer has to be sorted in any way.

==========
Teylyn, yours would still return wrong results unless the search value is LESS than the value in B1.

[verisme]

thanks everyone ! for the answer
this is my formula lookup

=LOOKUP(F7,Sheet1!$B$3:$B$50,Sheet1!$A$3:$A$50)

if i've type the data "LOST" but the lookup value is "LOST IN EXCEL" then what function should i use, and if the data that i've type is same as the lookup value i want the result value that turns up.

[JBeaucaire]

To search within variable length text strings, maybe this:

=INDEX($A$1:$A$50,SUM(--ISNUMBER(SEARCH(F7,$B$1:$B$50))*ROW($B$1:$B$50)))

...confirmed by pressing CTRL-SHIFT-ENTER to activate an array. If you just press ENTER, it won't work and you'll get an error. Using CTRL-SHIFT-ENTER will cause curly braces { } to appear around your formula to indicate the array is active.

[verisme]

sorry, i think that you misunderstanding what i want, this is the example of my formula and what i want, please have a look and tell me the solution.

[teylyn]

Hi,

the way your data is layed out, you can try this formula:

Please Login or Register  to view this content.

If you swap the columns in Sheet1, so that the text is in column A and the numbers are in column B you can use a simple Vlookup instead of INDEX/MATCH, like this

Please Login or Register  to view this content.

hth

[verisme]

thanks a lot @teylyn that solve my problem !.