How to return multiple instances using VLOOKUP
 

I'm using data similar to the following:
On one page, in Column A is a sequence of numbers from 100-140.
On another page, Column A and C are the following:
103 $500
106 $275
108 $145
110 $800
103 $615
108 $420

I have a VLOOKUP formula that will search for the number in Column A on each
row of the first page in Column A on the second page and then return the
value of the same row in Column B of the second page. The problem is that it
only returns the first value and then moves on. In the example, it would
return only $500 for 103 and would never see the next $615. How do I get it
to return more than one instance? I'd like to be able to show the instances
in the same row and then provide a sum at the end. It seems like I should
just be able to reference what instance of the VLOOKUP I want or to make it
so it searches the rows after the first instance. Any help is appreciated.

How to return multiple instances using VLOOKUP
 

Jay,

Use a pivot table based on your data table to get the totals, and use filters on the original data
when yoiu want to see the specifics for a certain number.

HTH,
Bernie
MS Excel MVP


"Jaybisco" wrote in message
...
I'm using data similar to the following:
On one page, in Column A is a sequence of numbers from 100-140.
On another page, Column A and C are the following:
103 $500
106 $275
108 $145
110 $800
103 $615
108 $420

I have a VLOOKUP formula that will search for the number in Column A on each
row of the first page in Column A on the second page and then return the
value of the same row in Column B of the second page. The problem is that it
only returns the first value and then moves on. In the example, it would
return only $500 for 103 and would never see the next $615. How do I get it
to return more than one instance? I'd like to be able to show the instances
in the same row and then provide a sum at the end. It seems like I should
just be able to reference what instance of the VLOOKUP I want or to make it
so it searches the rows after the first instance. Any help is appreciated.

How to return multiple instances using VLOOKUP
 

=Vlookup() returns a single value.

If you just wanted the sum, you may want to look at =sumif().



Jaybisco wrote:

I'm using data similar to the following:
On one page, in Column A is a sequence of numbers from 100-140.
On another page, Column A and C are the following:
103 $500
106 $275
108 $145
110 $800
103 $615
108 $420

I have a VLOOKUP formula that will search for the number in Column A on each
row of the first page in Column A on the second page and then return the
value of the same row in Column B of the second page. The problem is that it
only returns the first value and then moves on. In the example, it would
return only $500 for 103 and would never see the next $615. How do I get it
to return more than one instance? I'd like to be able to show the instances
in the same row and then provide a sum at the end. It seems like I should
just be able to reference what instance of the VLOOKUP I want or to make it
so it searches the rows after the first instance. Any help is appreciated.

--

Dave Peterson

How to return multiple instances using VLOOKUP
 

Jaybisco wrote:
I'm using data similar to the following:
On one page, in Column A is a sequence of numbers from 100-140.
On another page, Column A and C are the following:
103 $500
106 $275
108 $145
110 $800
103 $615
108 $420

I have a VLOOKUP formula that will search for the number in Column A on each
row of the first page in Column A on the second page and then return the
value of the same row in Column B of the second page. The problem is that it
only returns the first value and then moves on. In the example, it would
return only $500 for 103 and would never see the next $615. How do I get it
to return more than one instance? I'd like to be able to show the instances
in the same row and then provide a sum at the end. It seems like I should
just be able to reference what instance of the VLOOKUP I want or to make it
so it searches the rows after the first instance. Any help is appreciated.

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=VLookups(103,a1:b6,2) will return a vertical array of the values
corresponding to 103.

Alan Beban