continue VLOOKUP to next value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rich - 24 May 2006 00:51 GMT

I'm using VLOOKUP function to search through col A to extract data from
col B within the same row. However, there are multiple data in col A,
which fulfil the condition defined in VLOOKUP. The function finds only
the first cell that fulfills the condition. Is there any way how to
repeat/continue VLOOKUP search for next matching value bellow in col A
and thus place several extracted values from col B to new a column?
Thanks

Reply to this Message

Max - 24 May 2006 02:02 GMT

Here's a play using non-array formulas ..

Assume source data in cols A and B, row2 down to row100
Input the lookup value in C1

Put in C2:
=IF(ISERROR(SMALL(D:D,ROW()-1)),"",INDEX(B:B,MATCH(SMALL(D:D,ROW()-1),D:D,0)))

Put in D2: =IF(A2="","",IF(A2=$C$1,ROW(),""))
(Leave D1 empty)

Select C2:D2, fill down to D100
(cover the extent of the source data)

Col C will return the results from col B for the lookup value input in C1,
with all results neatly bunched at the top

(You could also use data > filter > autofilter on col A)

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I'm using VLOOKUP function to search through col A to extract data from
> col B within the same row. However, there are multiple data in col A,
[quoted text clipped - 3 lines]
> and thus place several extracted values from col B to new a column?
> Thanks

Reply to this Message