VLOOKUP - more than one return required

[Guest]

My range of data (table array) has more than one row that meet the VLOOKUP
criteria I have set.

I have a VLOOKUP in eg A1 =VLOOKUP($K$1,Order_Log,6,FALSE). In this I want
the first value to be returned (as VLOOKUP does per standard, using FALSE).
This I can do.

In cell A2 however, I would like to return the 2nd value from my range of
data.

In cell A3, the 3rd ....and so on (only up until around the 6th value).

What do I need to add into my A1 formula, for the 2nd value to be returned
in A2? I guessed it may be as easy as FALSE +1, but this does not seem to
work!

Many thanks

Jenny

 

[Guest]

One way, let's say the column that contains your keys is in L1:L10, the
column with the data you want returned is in M1:M10, and K1 contains the
value you want to look up:

IF(ROW()>COUNTIF($L$1:$L$10,$K$1),"",INDEX($M$1:$M$10,SMALL(IF($L$1:$L$10=$K$1,ROW(INDIRECT("1:"&ROWS($L$1:$L$10))),""),ROW())))

The item that will be returned will be dependent on the row in which the
formula is entered (if entered in cell C8, it will return the 8th item). You
could use a cell reference that specifies which item to return instead of
Row(), then you're not locked into having to enter the formula in a specific
row.

 

[Guest]

Thanks JMB,

Could you give me an example of say, the cell I'm entering this formula into
is C8, but I actually require the 2nd item to be returned?

Many thanks

Jenny

 

[Guest]

Enter 2 in another cell (say B8) and change Row() to B8, so then the formula
becomes:

=IF(B8>COUNTIF($L$1:$L$10,$K$1),"",INDEX($M$1:$M$10,SMALL(IF($L$1:$L$10=$K$1,ROW(INDIRECT("1:"&ROWS($L$1:$L$10))),""),B8)))

You could hardcode the 2 into the formula, but it is easier to work with
when using an external cell reference to contain your criteria.

Also, I don't remember if I stated earlier that this is an array formula.
After keying it into the formula bar, you must hit Control+Shift+Enter.