Vlookup

  • Thread starter StephenAccountant
  • Start date
S

StephenAccountant

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

ScottO

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


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

Biff

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
 
B

Biff

Ooops!

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

Should be:

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

Biff
 
S

StephenAccountant

Yes you are correct. How do I implement this into my spreadsheet. Any help
would be appreciated.
 
S

ScottO

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