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.
Posted by Aladin Akyurek on August 14, 2001 4:48 PM
James,
You don't need VLOOKUP for this task.
Lets assume that the SpeciesID is in A from A2 on, Pres/Absent in B, and SiteID in C.
Select all of the cells with species ID's and name the selected range by typing SPECIESLIST in the Name Box (see Formula Bar).
Select the range (from B2 on) that contains presence/absence and name it also (e.g., PLIST). Finally, create the name SITELIST for the range of the site ids.
Create (in a different worksheet, if you like) a unique list of species in column A from A2 on. And a unique list of sites across the first row from B1 on. (Both can be done using Adv Filter.)
In B2 enter: =SUMPRODUCT((SPECIESLIST=$A2)*(PLIST="+")*(SITELIST=B$1))
Copy this formula across first then down. It will give you a count of how many times each species has been observed at a given site. A zero means of course: never observed at the site under consideration.
If you'd like to see just a plus or nothing in the new table, use
=IF(SUMPRODUCT((SPECIESLIST=$A2)*(PLIST="+")*(SITELIST=B$1))>0,"+","")
Aladin
PS. Are you ethologist?
Posted by Richard S on August 14, 2001 5:17 PM
Pivot Table?
Aladin
A pivot table could also work in this situation?
Posted by Aladin Akyurek on August 14, 2001 5:26 PM
Re: Pivot Table?
Yep.
I wanted to give him something that is easy and fully automatic (no manual refresh). Please don't take this as a stance against Pivot Tables.
Aladin
Posted by James Coleman on August 15, 2001 5:42 AM
Aladin,
Thanks for your response. Concerning my field of study, I'm a biologist (PhD student) at Univ. of Cincinnati, Ohio.
Jamie
