VLOOKUP within a subset of data
Posted by Michael Jones on May 11, 2001 3:33 PM
In my data sheet there are three variables - column 1 data ("X"), Column 2 ("Y") and Column 3 ("Z"). In my display sheet I need a formula that will take my "X" data and bring back from my data sheet the corresponding "Z" value, given a specific "Y" value. My band-aid solution gets me a yes/no response: IF(ISNA(VLOOKUP($A10&D$1,'By DMA'!$A$3:$O$1843,1,))=TRUE,"no","yes"). In order to get this I created an column in my data sheet X&Y. If the Z data exists I get a yes. I need the actual "Z" data. Pleae Help.
Posted by Kevin James on May 11, 2001 3:58 PM
I have written such a VLookup formula. But the criteria is that both columns X and Y be sorted. so that Y is a somewhat a subset of X, as in:
X Y Z
fruit apples 10
fruit bananas 20
nuts cashews 5
nuts peanuts 35
If this is not the case with your data, then you might take a look at the "DATA" functions in Excel such as DSUM, etc.
Kevin
