VLOOKUP on Filtered Results?

[Peter Bernadyne]

Hello,

Does anybody know if it is possible to use VLOOKUP on a range of cell
that have been filtered such that only the visible universe of filtere
cells forms the source for the VLOOKUP?

Let's say I have 10 data items in a column and my filter give
datapoints 1, 3, and 7 (with the rest in the hidden rows in filtere
mode).

I wish to VLOOKUP only from datapoints 1,3,7 (since they meet th
criteria I am interested in) and return null values for othe
datapoints (2, 4, 5,6,8,9,10) since these don't meet the filte
criteria I'm interested in. Obviously, I could separate out everythin
into pre-filtered workspaces, but I'd rather not do that if there's
way to contain my universe to a filtered list.

Thanks in advance for any advice.

-Pete

 

[Myrna Larson]

Are you using VLOOKUP for an exact match? If so, you could add another column
to the left of your existing table with this formula which filters out the
unwanted data and replaces with empty text. Then do your lookup on this
column.

=IF(OR(B1=1,B1=3,B1=7),B1,"")

But if you are using a range lookup, this won't work. In that case I think
you'll have to copy the data to be searched to a new table.

 

[Frank Kabel]

Hi
lets assume you look for the value in column A and want to return the
value from column C. Then try the following array formula (entered with
CTRL+SHIFT+ENTER):
=INDEX($C$1:$C$10,MATCH(1,($A$1:$A$10=lookup_value)*(SUBTOTAL(3,OFFSET(
$A$1,ROW($A$1:$A$10)-1,0))=1),0))

--
Regards
Frank Kabel
Frankfurt, Germany

"Peter Bernadyne" <[email protected]>
schrieb im Newsbeitrag
news[email protected]...