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
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
| >
| >
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.
| >
| >
| >