VLOOKUP

S

Scott

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!
 
N

Niek Otten

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

Scott

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!
 
Z

Zack Barresse

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

Scott

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

RagDyer

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!
==============================================
 
A

Alan Beban

Scott said:
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!
Click to expand...
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
 
Top