Hello,
How can I get VLOOKUP to continue searching a column after it finds the
value it is looking for? For example..we have the following in A1 (NEW YORK)
and in A2 (NEW YORK). I am able to pull up the values in b1 (# of employees)
and c1 (total # of phones), but it does not seem to search passed A1 once the
value is found.
Thanks in advance!
Hi Scott,
What do you wish Excel to do?
Overwrite the first values found?
Give more values and thereby possibly overwrite others formulas?
Please elaborate on what you're trying to achieve.
--
Kind regards,
Niek Otten
"Scott" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> How can I get VLOOKUP to continue searching a column after it finds the
> value it is looking for? For example..we have the following in A1 (NEW
> YORK)
> and in A2 (NEW YORK). I am able to pull up the values in b1 (# of
> employees)
> and c1 (total # of phones), but it does not seem to search passed A1 once
> the
> value is found.
>
> Thanks in advance!
Hello Scott,
Maybe you could look at the VLOOKUPNTH by petermoran...
http://vbaexpress.com/kb/getarticle.php?kb_id=8
HTH
--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)
"Scott" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> How can I get VLOOKUP to continue searching a column after it finds the
> value it is looking for? For example..we have the following in A1 (NEW
> YORK)
> and in A2 (NEW YORK). I am able to pull up the values in b1 (# of
> employees)
> and c1 (total # of phones), but it does not seem to search passed A1 once
> the
> value is found.
>
> Thanks in advance!
Hello Niek and Zack..thanks for the replies.
Niek: I do not want the previous value found overwritten. I'll give you an
example of what I am trying to achieve.
If I have NEW YORK in A1 and A2 with values in b1, c1 and b2 and c2, I want
to be able to type in NEW YORK and get the values in:
b1 c1
b2 c2
Hope thats clearer. Thanks again!
"Zack Barresse" wrote:
> Hello Scott,
>
> Maybe you could look at the VLOOKUPNTH by petermoran...
>
> http://vbaexpress.com/kb/getarticle.php?kb_id=8
>
> HTH
>
> --
> Regards,
> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>
>
>
> "Scott" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > How can I get VLOOKUP to continue searching a column after it finds the
> > value it is looking for? For example..we have the following in A1 (NEW
> > YORK)
> > and in A2 (NEW YORK). I am able to pull up the values in b1 (# of
> > employees)
> > and c1 (total # of phones), but it does not seem to search passed A1 once
> > the
> > value is found.
> >
> > Thanks in advance!
>
>
>
Did you check out the link I provided? It comes with directions and even a
sample file. There are, of course, ways you can do this with a system of
native functions, but as for myself, I find them too time consuming and I
resort to VBA to write custom functions, as is with the link provided.
--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)
"Scott" <[email protected]> wrote in message
news:[email protected]...
> Hello Niek and Zack..thanks for the replies.
>
> Niek: I do not want the previous value found overwritten. I'll give you an
> example of what I am trying to achieve.
>
> If I have NEW YORK in A1 and A2 with values in b1, c1 and b2 and c2, I
> want
> to be able to type in NEW YORK and get the values in:
> b1 c1
> b2 c2
>
> Hope thats clearer. Thanks again!
>
>
> "Zack Barresse" wrote:
>
>> Hello Scott,
>>
>> Maybe you could look at the VLOOKUPNTH by petermoran...
>>
>> http://vbaexpress.com/kb/getarticle.php?kb_id=8
>>
>> HTH
>>
>> --
>> Regards,
>> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>>
>>
>>
>> "Scott" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hello,
>> >
>> > How can I get VLOOKUP to continue searching a column after it finds the
>> > value it is looking for? For example..we have the following in A1 (NEW
>> > YORK)
>> > and in A2 (NEW YORK). I am able to pull up the values in b1 (# of
>> > employees)
>> > and c1 (total # of phones), but it does not seem to search passed A1
>> > once
>> > the
>> > value is found.
>> >
>> > Thanks in advance!
>>
>>
>>
Yes Zack..thanks! I am trying that method out too. The only thing is (for me)
I am not very familiar with VBA..so if there was a native function that could
do it, I might be a little more comfortable with it.
"Zack Barresse" wrote:
> Did you check out the link I provided? It comes with directions and even a
> sample file. There are, of course, ways you can do this with a system of
> native functions, but as for myself, I find them too time consuming and I
> resort to VBA to write custom functions, as is with the link provided.
>
> --
> Regards,
> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>
>
>
> "Scott" <[email protected]> wrote in message
> news:[email protected]...
> > Hello Niek and Zack..thanks for the replies.
> >
> > Niek: I do not want the previous value found overwritten. I'll give you an
> > example of what I am trying to achieve.
> >
> > If I have NEW YORK in A1 and A2 with values in b1, c1 and b2 and c2, I
> > want
> > to be able to type in NEW YORK and get the values in:
> > b1 c1
> > b2 c2
> >
> > Hope thats clearer. Thanks again!
> >
> >
> > "Zack Barresse" wrote:
> >
> >> Hello Scott,
> >>
> >> Maybe you could look at the VLOOKUPNTH by petermoran...
> >>
> >> http://vbaexpress.com/kb/getarticle.php?kb_id=8
> >>
> >> HTH
> >>
> >> --
> >> Regards,
> >> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
> >>
> >>
> >>
> >> "Scott" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Hello,
> >> >
> >> > How can I get VLOOKUP to continue searching a column after it finds the
> >> > value it is looking for? For example..we have the following in A1 (NEW
> >> > YORK)
> >> > and in A2 (NEW YORK). I am able to pull up the values in b1 (# of
> >> > employees)
> >> > and c1 (total # of phones), but it does not seem to search passed A1
> >> > once
> >> > the
> >> > value is found.
> >> >
> >> > Thanks in advance!
> >>
> >>
> >>
>
>
>
Say your datalist was on Sheet2, from A1 to C50.
On Sheet1, you want to enter a city, in say F1, and have the corresponding
data display in Columns G and H for all the occurrences in your Sheet2
datalist that match the city in F1.
Enter this *array* formula in G1 of Sheet1:
=INDEX(Sheet2!B$1:B$50,SMALL(IF(Sheet2!$A$1:$A$50=$F$1,ROW($A$1:$A$50)),ROW(
1:1)))
--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
Then, select G1 and drag it to H1 to copy.
Now, select *both* G1 and H1 and drag down to copy as many rows as you
anticipate there are occurrences in your datalist of the city you entered in
F1.
You should copy down until you return a #NUM! error, meaning that you have
returned all the occurrences present, and have run out of matching data.
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
"Scott" <[email protected]> wrote in message
news:[email protected]...
> Yes Zack..thanks! I am trying that method out too. The only thing is (for
me)
> I am not very familiar with VBA..so if there was a native function that
could
> do it, I might be a little more comfortable with it.
>
> "Zack Barresse" wrote:
>
> > Did you check out the link I provided? It comes with directions and
even a
> > sample file. There are, of course, ways you can do this with a system
of
> > native functions, but as for myself, I find them too time consuming and
I
> > resort to VBA to write custom functions, as is with the link provided.
> >
> > --
> > Regards,
> > Zack Barresse, aka firefytr, (GT = TFS FF Zack)
> >
> >
> >
> > "Scott" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hello Niek and Zack..thanks for the replies.
> > >
> > > Niek: I do not want the previous value found overwritten. I'll give
you an
> > > example of what I am trying to achieve.
> > >
> > > If I have NEW YORK in A1 and A2 with values in b1, c1 and b2 and c2, I
> > > want
> > > to be able to type in NEW YORK and get the values in:
> > > b1 c1
> > > b2 c2
> > >
> > > Hope thats clearer. Thanks again!
> > >
> > >
> > > "Zack Barresse" wrote:
> > >
> > >> Hello Scott,
> > >>
> > >> Maybe you could look at the VLOOKUPNTH by petermoran...
> > >>
> > >> http://vbaexpress.com/kb/getarticle.php?kb_id=8
> > >>
> > >> HTH
> > >>
> > >> --
> > >> Regards,
> > >> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
> > >>
> > >>
> > >>
> > >> "Scott" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> > Hello,
> > >> >
> > >> > How can I get VLOOKUP to continue searching a column after it finds
the
> > >> > value it is looking for? For example..we have the following in A1
(NEW
> > >> > YORK)
> > >> > and in A2 (NEW YORK). I am able to pull up the values in b1 (# of
> > >> > employees)
> > >> > and c1 (total # of phones), but it does not seem to search passed
A1
> > >> > once
> > >> > the
> > >> > value is found.
> > >> >
> > >> > Thanks in advance!
> > >>
> > >>
> > >>
> >
> >
> >
Scott wrote:
> Hello,
>
> How can I get VLOOKUP to continue searching a column after it finds the
> value it is looking for? For example..we have the following in A1 (NEW YORK)
> and in A2 (NEW YORK). I am able to pull up the values in b1 (# of employees)
> and c1 (total # of phones), but it does not seem to search passed A1 once the
> value is found.
>
> Thanks in advance!
For another approach:
Highlight a1:c10; click on Data|Filter|AutoFilter; click on the down
arrow for Column A and select NewYork (or whatever city you're
interested in.
Alan Beban
Thanks everyone for the suggestions! Helped a great deal!!!
"Alan Beban" wrote:
> Scott wrote:
> > Hello,
> >
> > How can I get VLOOKUP to continue searching a column after it finds the
> > value it is looking for? For example..we have the following in A1 (NEW YORK)
> > and in A2 (NEW YORK). I am able to pull up the values in b1 (# of employees)
> > and c1 (total # of phones), but it does not seem to search passed A1 once the
> > value is found.
> >
> > Thanks in advance!
> For another approach:
>
> Highlight a1:c10; click on Data|Filter|AutoFilter; click on the down
> arrow for Column A and select NewYork (or whatever city you're
> interested in.
>
> Alan Beban
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks