LOOKUP

  • Thread starter Thread starter Dthmtlgod
  • Start date Start date
D

Dthmtlgod

Hello all.

Having a problem with LOOKUP.

Here is my formula, pretty basic
=LOOKUP(N2,LEAR!$C$2:$C$1000,LEAR!$A$2:$A$1000)

I want to look up the value of N2 in the Lear spreadsheet in column C and if
it finds a match, return the value of column A. It returns a value even if
it doesn't find a match. Column C is sorted in ascending order.

I also get a lot of NAs on values that start with numbers. I can live with
this.

Please let me know if I am doing anything wrong.

Thanks
 
LOOKUP(lookup_value,array)

Lookup_value is a value that LOOKUP searches for in an array. Lookup_value
can be a number, text, a logical value, or a name or reference that refers
to a value.

a.. If LOOKUP can't find the lookup_value, it uses the largest value in
the array that is less than or equal to lookup_value.

b.. If lookup_value is smaller than the smallest value in the first row or
column (depending on the array dimensions), LOOKUP returns the #N/A error
value

So, you may prefer vlookup or match/index
 
Don. OK. Thanks for the response.

I think I am still doing something wrong. I tried to use VLOOKUP as you
suggested.

Here is the forumla

=VLOOKUP(C30,LEAR!$A$2:$V$1000,1)

It is still returning a value when it doesn't find a match. I assume the
table array A2:V1000, that is where it it looking for the value of C30?,
then it returns the info in column index 1 (which is what I want).

I think I am close.

Please let me know.
 
Again, from HELP
a.. If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses
the largest value that is less than or equal to lookup_value.
b.. If lookup_value is smaller than the smallest value in the first column
of table_array, VLOOKUP returns the #N/A error value.
c.. If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP
returns the #N/A value.
Maybe you need to look in HELP for MATCH.
 
Also tried the INDEX and MATCH and it is returning a value everytime,
regarless if it finds a match

=INDEX(LEAR!$A$2:$C$1000,MATCH(N29,LEAR!$C$2:$C$1000,1),1)

=INDEX(LEAR!$A$2:$C$1000,MATCH(N29,LEAR!$C$2:$C$1000,1),1)

Change the match type to 0, returns NA.

Do you see anything wrong with this?
 
Thanks Don, the MATCH/INDEX did the trick.

Don Guillett said:
Again, from HELP
a.. If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses
the largest value that is less than or equal to lookup_value.
b.. If lookup_value is smaller than the smallest value in the first column
of table_array, VLOOKUP returns the #N/A error value.
c.. If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP
returns the #N/A value.
Maybe you need to look in HELP for MATCH.
Click to expand...
 
Hi

=VLOOKUP(C30,LEAR!$A$2:$V$1000,0)
returns the match or error when not found. To elliminate errors markings
=IF(ISERROR(VLOOKUP(C30,LEAR!$A$2:$V$1000,0)),"",VLOOKUP(C30,LEAR!$A$2:$V$10
00,0))
 
Back
Top