Ooops!

Typo:

=INDEX(D2:D101,SMALL(IF(A2:A101="yes",ROW(1:100)), ROW(INDIRECT("A"&A1))))

Should be:

=INDEX(D2:D101,SMALL(IF(A2:A101="yes",ROW(1:100)), ROW(INDIRECT("A"&B1))))

Biff

"Biff" wrote in message
...
Hi!

What determins which instance of "Yes" you want to lookup?

Assume you want the nth instance.

In cell B1 enter the instance you want:

B1 = 2

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=INDEX(D2:D101,SMALL(IF(A2:A101="yes",ROW(1:100)), ROW(INDIRECT("A"&A1))))

Biff

"StephenAccountant" wrote in
message ...
How do i use Vlookup for 100 rows when what I am searching for in the
vlookup
is the same refernce? EG:

Cell A1 is a heading.

In Cells A2 through to A101 I may or may not have a value. In fact I
will
usually only have about 2 or 3 of these cells with a value. The value is
the
text - Yes.

Now I have a formula that depending on if there is a Yes in the A column
then in my D column it will return a value.

So on my second worksheet I want to have Vlookup formula for the 100
rows
on my first worksheet where I look for the Yes in column A and then
return
the value in Column D.

But this doesn't work when I am referenceing the same thing in Column A.