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?