How to locate values in a different sheet ?
Posted by Thomas on November 14, 2001 7:05 AM
In an Excel file, I have a worksheet called
"String values" with a collection of data.
Each row has a key and an associated value.
The keys are in column B, the data is in column D.
The worksheet is sorted by the key column.
The key is not unique, i.e. depending on the
data source a single key can be present once,
more than once or not at all.
How can I display all values associated with
a specific key (e.g. 15) in a different worksheet?
I tried the function
LOOKUP(15,'String values'!B2:B1000,'String values'!D2:D1000)
but that leads to a problem if the key 15 is not
present: Instead of showing some kind of error,
the function takes the value for a neighboring key.
How can I get this right?
Posted by Dan on November 14, 2001 8:45 AM
See if this works for you:
=vlookup(15,'String values'!B2:D1000,3,False)
HTH
Posted by Thomas on November 14, 2001 11:08 AM
It works, another little question
Great, it works, thank you.
I got Excel 2000 here on my work PC.
Is the vlookup function also available
in Excel 97 or is it a new feature?
Thomas
Posted by Dan on November 14, 2001 11:24 AM
Yes, it works on Excel 97
Posted by katt on November 14, 2001 12:35 PM
Is it Possible to use formulas or cell references in the path names in the lookup? I have been trying rather unsuccessfully.
Posted by Dan on November 14, 2001 1:07 PM
Yes (if I am understanding what you are asking). Can you be specific as to what you are trying to do?
