Vlookup
 

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.

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.

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.

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.

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.

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