software

When lookup_value corresponds several value in table array, it only
catchs the first value/cell with vlookup function, how to catch the
second and more value?what do you want to do with em after they are caught?
You may be wanting to look at countif or sumif functions.

--
Don Guillett
SalesAid Software

quot;Valleyquot; gt; wrote in message oups.com...
gt; When lookup_value corresponds several value in table array, it only
gt; catchs the first value/cell with vlookup function, how to catch the
gt; second and more value?
gt;
One way - assuming your lookup table is in A1:B8, A14 contains your criteria,
enter in B14 (you must hold down Control Shift keys while hitting Enter when
you key these formulae in):

=INDEX(B$1:B$8,SMALL(IF(A$1:A$8=A$14,ROW(INDIRECT( quot;1:quot;amp;ROWS(A$1:A$8))),quot;quot;),ROWS(B$14:B14)))

Copy down until you get an error. Or, if you don't like error messages,

=IF(ROWS(B$14:B14)gt;COUNTIF(A$1:A$8,A$14),quot;quot;,INDEX( B$1:B$8,SMALL(IF(A$1:A$8=A$14,ROW(INDIRECT(quot;1:quot;amp;RO WS(A$1:A$8))),quot;quot;),ROWS(B$14:B14))))
quot;Valleyquot; wrote:

gt; When lookup_value corresponds several value in table array, it only
gt; catchs the first value/cell with vlookup function, how to catch the
gt; second and more value?
gt;
gt;


Valley wrote:
gt; When lookup_value corresponds several value in table array, it only
gt; catchs the first value/cell with vlookup function, how to catch the
gt; second and more value?

My meaning is

If the matching value is two or more, how to catch the second and the
third and more.

For example,
Value is 16444050(in columnA1 and A2) in sheet1
There are 16444050(in column A1 and A2) and 22401241(in column B1) and
224016549(in column B2) in sheet2

When using vlookup, it can only catch B1 but not B2 of sheet2, how to
catch B1 and B2, or how to catch B2 but not B1?See the quot;Arbitrary Lookupsquot; section he

cpearson.com/excel/lookups.htmIn article . comgt;,
quot;Valleyquot; gt; wrote:

gt; My meaning is
gt;
gt; If the matching value is two or more, how to catch the second and the
gt; third and more.
gt;
gt; For example,
gt; Value is 16444050(in columnA1 and A2) in sheet1
gt; There are 16444050(in column A1 and A2) and 22401241(in column B1) and
gt; 224016549(in column B2) in sheet2
gt;
gt; When using vlookup, it can only catch B1 but not B2 of sheet2, how to
gt; catch B1 and B2, or how to catch B2 but not B1?

This function only can catch small or big, but

My meaning is

Value is a column including many cells but not a cell, and several same
data corresponds maybe several different data in each cell, and I need
to catch all.
The menioned below is only a sample.JE McGimpsey wrote:
gt; See the quot;Arbitrary Lookupsquot; section he
gt;
gt; cpearson.com/excel/lookups.htm
gt;
gt;
gt; In article . comgt;,
gt; quot;Valleyquot; gt; wrote:
gt;
gt; gt; My meaning is
gt; gt;
gt; gt; If the matching value is two or more, how to catch the second and the
gt; gt; third and more.
gt; gt;
gt; gt; For example,
gt; gt; Value is 16444050(in columnA1 and A2) in sheet1
gt; gt; There are 16444050(in column A1 and A2) and 22401241(in column B1) and
gt; gt; 224016549(in column B2) in sheet2
gt; gt;
gt; gt; When using vlookup, it can only catch B1 but not B2 of sheet2, how to
gt; gt; catch B1 and B2, or how to catch B2 but not B1?Hi Valley,
One way is to insert a new column in between columns A and B then into
the top of the new (empty) column B paste and fill down this formula...

=A1 (COUNTIF(A:A,A1)-COUNTIF(A1:A$65536,A1))*0.0001This formula adds 0.0001*n to every succeeding recurrence of a column A
value, where n is the number of previous recurrences. If the number of
column A repetitions could exceed 10000 (as if!) you will have to
change the small number,0.0001, to a suitable smaller value so that
small number * greatest number of recurrences is less than 1.

So, your first 16444050 remains that value in the new column B and
doing a VLOOKUP based on the new column B will return 22401241.
The second 16444050 in column A is the second occurrence of that value,
so in the new column B it has been converted to 1644405.0001, so doing
a VLOOKUP with 16444405.0001 will return 224016549.
The next (3rd) occurrence of 16444050 would be converted to
16444050.0002 and this value should be used when trying to find the
VLOOKUP value in column B.

One way of setting up the VLOOKUP is, assuming C1:E2 are available...

In C1 the text quot;Column A Valuequot;
In C2, type in the column A value for the VLOOKUP to process
In D1 the text quot;Occurrencequot;
In D2, type in which occurrence. This is an integer between (and
including) 1 and the total number of occurences for that column A
value. If you exceed the total number the VLOOKUP just returns its
error result.
In E1 the text quot;Column B Valuequot;
In E2 the VLOOKUP formula...

=VLOOKUP(C2 (D2-1)*0.0001,B1:C65536,2,FALSE)

you might want to change the C65536 part of the address to indicate the
number of rows occupied by your data.

Hope this makes sense!
This is the way I have overcome the problem of recurring values in a
VLOOKUP table.

Ken JohnsonHi Johnson,
Thanks very much! This surely is a good way.

BR
ValleyKen Johnson wrote:
gt; Hi Valley,
gt; One way is to insert a new column in between columns A and B then into
gt; the top of the new (empty) column B paste and fill down this formula...
gt;
gt; =A1 (COUNTIF(A:A,A1)-COUNTIF(A1:A$65536,A1))*0.0001
gt;
gt;
gt; This formula adds 0.0001*n to every succeeding recurrence of a column A
gt; value, where n is the number of previous recurrences. If the number of
gt; column A repetitions could exceed 10000 (as if!) you will have to
gt; change the small number,0.0001, to a suitable smaller value so that
gt; small number * greatest number of recurrences is less than 1.
gt;
gt; So, your first 16444050 remains that value in the new column B and
gt; doing a VLOOKUP based on the new column B will return 22401241.
gt; The second 16444050 in column A is the second occurrence of that value,
gt; so in the new column B it has been converted to 1644405.0001, so doing
gt; a VLOOKUP with 16444405.0001 will return 224016549.
gt; The next (3rd) occurrence of 16444050 would be converted to
gt; 16444050.0002 and this value should be used when trying to find the
gt; VLOOKUP value in column B.
gt;
gt; One way of setting up the VLOOKUP is, assuming C1:E2 are available...
gt;
gt; In C1 the text quot;Column A Valuequot;
gt; In C2, type in the column A value for the VLOOKUP to process
gt; In D1 the text quot;Occurrencequot;
gt; In D2, type in which occurrence. This is an integer between (and
gt; including) 1 and the total number of occurences for that column A
gt; value. If you exceed the total number the VLOOKUP just returns its
gt; error result.
gt; In E1 the text quot;Column B Valuequot;
gt; In E2 the VLOOKUP formula...
gt;
gt; =VLOOKUP(C2 (D2-1)*0.0001,B1:C65536,2,FALSE)
gt;
gt; you might want to change the C65536 part of the address to indicate the
gt; number of rows occupied by your data.
gt;
gt; Hope this makes sense!
gt; This is the way I have overcome the problem of recurring values in a
gt; VLOOKUP table.
gt;
gt; Ken JohnsonHi Valley,
You're welcome.
Thanks for the feedback.
JMB's solution and Chip Pearson's Arbitrary Lookup both work.
Perhaps you weren't using Control Shift Enter when entering their
formulas.
Ken Johnson