LOOKUP?

[mailrail]

I need to match a 5-digit zip code with a person responsible for that area.
For instance, if I type in 49494, and I have a person responsible for the
entire 494 area, I'd like to return the value (person) who has that area
without have to do a matchup for each individual zip code. For instance:

I type in 49494. I have another list that shows this:
490 - Smith
491 - Jones
492 - Hayes
493 - Gates
494 - Doe

Thanks in advance!

 

[Pete_UK]

Suppose that list is in M1:N5, and you enter 49494 in A1 - put this in
B1:

=IF(ISNA(MATCH(--LEFT(A1,3),M$1:M$5,0)),"no-one",VLOOKUP(--LEFT(A1,3),M
$1:N$5,2,0))

I assume that the 490, 491 etc in column M have been entered as
numbers, so the -- in front of the LEFT functions ensures that the
lookup value is also a number.

Hope this helps.

Pete

 

[mailrail]

I keep coming up with no-one each time I try this. What am I doing wrong?

 

[Pete_UK]

That means that the formula is not finding an exact match. Maybe your
entries in column M are in fact text values, in which case you will
not need the double unary minus (--) in the formula. If they are text
values then you will need to be wary of other characters that might
have got in, such as spaces.

Or, it might be tht you don't have any exact matches.

Hope this helps.

Pete