Here's one way ...

Assuming that ColG is available ...
In G1 type any label you like for the helper column
In G2 put this formula =IF(A2="Yes",1,"")
In G3 put this formula =IF(A3="Yes",MAX($G$2:G2)+1,"")
Fill down from G3 to G101

Assuming that the 2 worksheets are called Sheet1 & Sheet2 (original aren't I
;) ...
And assuming that A1:B10 are vacant ...
In A1 type your helper column label
In A2:A10 fill with the numbers 1 through 9
In B1 put whatever label you want for the value returned from Sheet1 ColD
In B2 put this formula
=IF(ISNA(MATCH(SHEET2!A2,SHEET1!$G$1:$G$101,0)),"" ,INDEX(SHEET1!$D$1:$D$101,
MATCH(A2,SHEET1!$G$1:$G$101,0)))
Fill down to B10

This assumes that there won't be more than 9 "Yes" results on Sheet. If you
want more just drag A10 & B10 down as far as you want.

Let me know if this works for you or if you need a tweak or two.
Rgds,
ScottO

"StephenAccountant" wrote in
message ...
| Yes you are correct. How do I implement this into my spreadsheet. Any
help
| would be appreciated.
|
| "ScottO" wrote:
|
| If I understand your description correctly, you want the VLookUp formula
to
| return multiple answers (equal to the number of rows in sheet1 that
contain
| a "Yes" in ColA.
| As far as I know (and that's not all that far :), the VlookUp will only
| return one answer, i.e. as soon as it finds a match it stops.
| If I'm right, then I think that you'll need to add a helper column to
create
| a unique "Counter" type of value for each "Yes" row. Then on your sheet2
| you'll be able to do a table to extract all the "Yes" rows on the basis
of
| the unique "Counters".
| If you don't get a better answer, and you need some help implementing my
| suggestion, let me know.
| Rgds,
| ScottO
|
|
| "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.
|
|
|