Nested VLOOKUP or MACRO?
Posted by James Coleman on August 14, 2001 4:15 PM
I have species data ("+" or " " for presence/absence) in a single worksheet for a number of locations/sampling sites. The data are organized as follows:
SpeciesID Pres/Absent SiteID
Species1 "+"or" " Site1
Species2 "+"or" " Site1
Species3 "+"or" " Site1
Species1 "+"or" " Site2
Species2 "+"or" " Site2
Species3 "+"or" " Site2
and so on... (with a total species list of 450 and total site list of 183)
I am trying to create a data table with species1 through species450 in the first column and individual siteID across the top row with a column for each site. I need to have Excel search the data table (>6500 rows) to determine whether a species is present or absent at a given site. So I essentially have two LOOKUP values: SiteID and SpeciesID. I first need Excel to lookup the range of the data table dedicated to SiteX and then look to see if each of Species1...Species450 occur within the data range. A straight VLOOKUP appears inadequate for the task. Should I use a nested VLOOKUP? Or would a macro do the job? Thanks in advance for any help.