Matching and VLookups

[Jminer13]

I have just used some vlookups to find some information on a Unique
ID. Unfortunately each week a few of these ID's are deleted and some
are added- so a vlookup can not find information pertaining to them.

The good news is that the data is sorted in such a way that when a
vlookup returns a NA, b/c it can't find info on the related to the
ID,
that all data above it is what I need.

For instance, after running a Vlookup I left with:

ID's Region location
1 NY NYC
2 NA NA
3 CAL LA
4 NA NA
5 NA NA

*Note: ID's 2, 4 and 5 are new and therefore the Vlookup can't find
anything b/c there is no info on it. However, ID 2's, region and
location is the same as ID 1. Also, ID 4 and 5 region and location
info are the same as 3.

In saying and showing, is there any way to make a formula that would
find any NA returned by the Vlookup and match the region and
locations
associated with the ID to those above it.
Thank you.

 

[Earl Kiosterud]

J,

You could make a mirror table with these formulas, copied down and over, then use the
VLOOKUP against that. The VLOOKUP still looks in column A, but goes to column 4 for the
data to be retrieved. The first row (C) must have data, or it'll pick up "Region" or
"Location."

A B C D
ID's Region location
3 1 NY NYC =IF(B3<>"",B3,D2)
4 2 NA NA
5 3 CAL LA
6 4 NA NA
7 5 NA NA

--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...