vlookup

[Copying VLOOKUP formulas]

i'm doing a vlookup in column A. let's say a value appears more than once in
column A. the first vlookup result will be the first corresponding value
that appears, correct? how do I then alter the vlookup to find the 2nd
value? 3rd? 4th? 5th?

many thanks

 

[T. Valko]

Is column A sorted so that the entries are grouped or are the entries in
random order?

Is the value to be returned text or numeric or can it be either?

--
Biff
Microsoft Excel MVP


"Copying VLOOKUP formulas"

 

[Copying VLOOKUP formulas]

entries are in random order.

desired returned values are text and numeric.

 

[T. Valko]

Assume lookup table is in the range A2:B21

D2 = lookup value

Enter this array formula** in E2 and copy down until you get blanks:

=IF(ROWS(E$2:E2)<=COUNTIF(A$2:A$21,D$2),INDEX(B$2:B$21,SMALL(IF(A$2:A$21=D$2,ROW(B$2:B$21)-MIN(ROW(B$2:B$21))+1),ROWS(E$2:E2))),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Copying VLOOKUP formulas"