Lookup - Not all values in Array

[barnowl]

Hi there -

I am new here and fairly new to excel. I am receiving data from another organization, and need to match only those records that we deal with, and assign them the proper division. The incoming data includes many classes we are not concerned with. The problem I am encountering is due to the following

"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"

I do not want a value inserted if the class does not match our list. Here is an example of what I want to do -

Incoming Data -

Class
1
3
575

Our Data -

Class Division
1 100
2 101
3 102
...
...
50 125

When there is a class match, then I need the division number returned. If there is no match, then I need a n/a or "no Match" returned. I do not want it to look for the largest value <= in the array. The incoming data classes are not sequential and neither are our divisions.

Any advice - I keep going in circles?

thanks so much
Dawn

[rylo]

Dawn

Use VLOOKUP instead of LOOKUP. Along the lines of

=vlookup(value, lookuprange,offsetvalue,false)

The false at the end is important as it looks for an exact match.

=vlookup(a1, c1:d15,2,false)

HTH

rylo

[barnowl]

thanks Rylo - worked perfectly! Should have thought to ask here weeks ago.

Dawn